Using Excel - 3

Home ] What's new! ] Suspension types ] Tutorials ] Downloads ] Data files ] FAQ ] Purchase here ] Contact us ]

Creating Excel worksheets for front and rear suspensions

horizontal rule

This Excel tutorial uses Excel V16. Check the supported versions of Excel here.

This tutorial demonstrates a method for creating a single Excel workbook, containing worksheets for front and rear suspensions.

This tutorial uses the SRD912 demo file, but you can use your own data.

Start SusProg3D, and open the demo file, SRD912.s3d

Go to the Tools tab, and then Calc2Excel. This will open the "Export calculated data to Excel" dialog.
If the number of calculated data points is greater than the Excel range, then all Excel cells will be blank, so make sure that the Excel range size matches the Roll&Bump calculation range.
The required number of cells is shown in the "Export calculated data to Excel" dialog.

We will assume that you have completed the Using Excel to graph Susprog3D data tutorial and that you are familiar with creating named ranges.

Start Excel, and open a new Workbook (if there is not one already open).
Note that the Excel title bar will show 'Microsoft Excel - Book1'

For this tutorial, I will create two sets of named cells.

Make sure you are on 'Sheet1'.

Add some text to cell A1, "Travel", B1 "Camber"

This tutorial assumes 11 data points in bump and droop. If you have more data points, then increase the range of cells by the appropriate number in the following instructions.
For example, if you have 21 data points then A17 will become A22, B17 will become B22, etc.

Hint: If the front and rear suspensions have different range sizes then use the bigger size to create your named ranges. This will save a bit of extra work.

Select the block of 11 cells A2 to A12 and name this range S3D_B_Point_Bump, change the Scope to Sheet1, and then OK.
Repeat with the second block of cells, B2 to B17, and name as S3D_B_Point_Camber with Scope Sheet1, and OK.

Notice that (just under the 'Open Excel' button) it shows 'Book1'. This is the name of the current Excel workbook.
The worksheet name shows that Sheet1 will be used for all data, Front LH, Front RH, Rear LH and Rear RH.
Note also that the datum side is shown, LH in this case.
Don't close the "Export to Excel" dialog. You can minimize it, just don't close it.

Because we didn't append the range names with "LH" or "RH", we will be working with the datum side. The datum is side is the one shown in brackets. Switch it if you prefer the opposite side.

Go to Roll&Bump. Make sure that you are using [Front], and then do a Roll&Bump Calc.
Because Excel was open, SusProg3D will connect to the currently open workbook (Book1) and the worksheet specified for Front LH(Sheet1)
Both the Travel and Camber cells should have updated.

Go to Roll&Bump, and this time change to [Rear], and then do a Roll&Bump Calc.
Because Excel was open, SusProg3D will connect to the currently open workbook (Book1) and the specified worksheet for Rear LH (which is also Sheet1).
Both the Travel and Camber cells should have updated. But with the rear data!

So now we need to create bespoke 'Front' and 'Rear' worksheets.

Change the worksheet name from 'Sheet1' to 'Front' (or any other suitable name). And make sure that you add all the named ranges to the 'Front' sheet that you will need.
This saves having to create them twice (once for the front, and a second time for the rear).

Now, we need to copy the 'Front' sheet.
Right click on the 'Front' sheet tab then click Move or Copy Sheet.
Where the dialog says 'Before sheet', select 'Sheet2'.
Then 'tick' the 'Create a copy' box, then OK.

Excel will copy the 'Front' sheet, and create a new sheet called 'Front (2)'. Rename it to 'Rear'.

In the Calc2Excel tool, click on the worksheet refresh button so that it can update with the new worksheet names.
If you have used the names 'Front' and 'Rear' then Calc2Excel will use 'Front' for Front LH and RH, and 'Rear' for Rear LH and RH.
If you have used different names then you will need to change the selection in each of the worksheet name combo boxes to the appropriate name.

Now use Formulas -> Name Manger to see all the named ranges. Note that all names should be scoped to their specific sheets.

Make sure that the data (ie cells A2:B12) on both sheets is blank.

Back to SusProg3D and change to [Front].
Go to Roll&Bump and do a Roll&Bump Calc.
Calc2Excel now has linked worksheet 'Front' to both Front LH and Front RH, and updated the Travel and Camber cells. Verify that the rear sheet has not been updated.

Change to [Rear] and do a Roll&Bump Calc.
Calc2Excel now has linked worksheet 'Rear' to both Rear LH and Rear RH, and updated the Travel and Camber cells. Verify that the front sheet has not been updated.

For the front and rear calculations we have just done, we could have used ECalc with the "Roll and Bump" ticked. The results are the same.

We could also use the VCalc dialog. The difference is that ECalc only does one end of the vehicle whereas VCalc does both ends of the vehicle.
If you use VCalc with the "Roll and Bump" ticked, then both front and rear worksheets will be updated.

So what we have now done, is to ensure that the front and rear data is correctly identified in the Excel workbook, and that only that data for the appropriate end of the vehicle is updated.

Save Excel, and you can use it for next tutorial.

Tutorial developed using Excel V16 and SusProg3D V5.106A on Windows 10 (64-bit) Nov 2020