Flexible Interactive Workbooks in Excel

Tutorial 3

Creating the main page

It is now time to create the page that the students will see. It is to contain the names of the units for the conversion selected, the value chosen in both units and the conversion factor to convert both ways.

The following steps will work through the stages described above.

1. Open toolbars

   • From the menu, select 'View, Toolbars' and, if not already selected, tick Drawing, Forms and Visual Basic. The Standard and Drawing toolbars should already be ticked. (fig. 6.)

2. Create a reference for the required conversion

   • In cell "G3", simply enter 1. This will create a reference for the table on the settings sheet. (It will be controlled by a spinner later.)

3. The units of the conversion

   • Click in cell "B3" then click the paste function button on the toolbar. Select the "Lookup & reference" category, then "VLOOKUP" from the list of available functions, (fig. 7.). In the Lookup_value box, type G3, or click on cell "G3" on the worksheet. This tells the spreadsheet to look for the value in cell "G3" in the first column of the table. Click in the Table_array box, then select the settings sheet using the tab at the bottom of the page. Now highlight the whole of the reference table on this worksheet. Click in the Col_index_num box and type 2, (fig. 8.). This tells the spreadsheet to look in the second column of the table. Click ok and you should see cm in cell "B3".

   • Repeat this step for cell "D3", using 3 as the Col_index_number. This should display inches in the cell.

   • Now try changing the value in cell "G3" to a number between 1 and 10. The units should change in cells "B3" and "D3".

4. The conversion factors

   • This again uses the VLOOKUP function. For cells "C2" and "C5" follow the stages above, using 4 and 5 respectively as the Col_index_number.

5. The Unit values

   • Firstly type 1 in cell "B4". This will be the users selected value. It will be controlled by a scroll bar later.

   • In cell "D4", type the formula =B4*C3, this will convert the original value into the new units. The sheet should now look like fig. 9.

Now move on to Step 4