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 Excel2007 or later. 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 the block of 11 cells A2 to A17. Then go to Formulas -> Define Name. Name this group S3D_Bump, change the Scope to Sheet1, and then OK.
Repeat with the second block of cells, B2 to B17, and name as S3D_Camber with Scope Sheet1

Close and reopen the "Export calculated data to Excel" dialog. This will connect the "Export calculated data to Excel" dialog 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 the 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.
Click on the 'Front' sheet tab (to make sure that is the current sheet).
On the Home tab, in the Cells group, click Format and then under Organize Sheets, click Move or Copy Sheet.
You can also right-click on a selected sheet tab, and then click Move or Copy.
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 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: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.

Tutorial developed using Excel2003 and SusProg3D V4.516B on Windows 7 Ultimate (64-bit) 13 Jan 2013