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

This Excel tutorial requires Excel2003 or earlier. 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.

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 16 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.

Select a the block of cells A2 to A17. Then go to Insert -> Name -> Define, and name this group as s3d_Bump, then "OK"
Repeat with the second block of cells, B2 to B17, and name as s3d_Camber

Shortcut hint. If you don't need your own names, use the group names. Put "s3d_bump" in A1, "s3d_Camber" in B1. Then Excel will automatically use these as the range names.

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, and connect to the open workbook.

Notice that (just under the 'Open Excel' button) it shows 'Book1'. This is the name of the current Excel workbook.
Note also that the datum side is shown.
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 currently active worksheet (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 currently active worksheet (Sheet1)
Both the Travel and Camber cells should have updated. But with the rear data!

Now, back to Excel, and rename 'Sheet1' to 'Front', then click on 'Sheet2' to make it the active sheet.

Go to Roll&Bump, change to [Front], and then do a Roll&Bump Calc.
Because there is a worksheet named 'Front', SusProg3D will make it currently active worksheet, and update the Travel and Camber cells.

Go to Roll&Bump, change to [Rear], and then do a Roll&Bump Calc.
Because there is not a worksheet named 'Rear', and the active worksheet is 'Front', no cells have been updated. This demonstrates the safeguard we have by naming the worksheet 'Front', that an inadvertent calculation of the rear data will not overwrite the front data.

So now we need to create a 'Rear' worksheet.

But before we do, 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.
Make sure that 'Front' is the current sheet.
Click on the 'Edit' menu, then the 'Move or Copy sheet' command. The shortcut is Alt+E, Alt+M
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'.

Now, in the Rear sheet, go to Insert, Name, Define and you will see all the named ranges, but with 'Rear' on the right hand side.

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

Back to SusProg3D and change to [Front].
Go to Roll&Bump and do a Roll&Bump Calc.
Because there is a worksheet named 'Front', SusProg3D will make it currently active worksheet, and update the Travel and Camber cells. Verify that the rear sheet has not been updated.

Change to [Rear] and do a Roll&Bump Calc.
Because there is a worksheet named 'Rear', SusProg3D will make it currently active worksheet, and update 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.

What you may prefer, is that the two source data sheets 'front' and 'rear' are updated by SusProg3D, and that your graphs and calculations are on other sheets, referencing back to the source data sheets.
This way you can see the results (perhaps graphs) in which you are interested as you do the SusProg3D calculations.