Learning Excell the Shadowrun way, Learning to make if then statements using drop down menus Options

[Kronk2]

I am making a spell design work book and am wanting to be able to have all the drain compnents as drop down menu items. (I am using excell 2003) So how do I do this. I have my drop down menus all ready to go. For example I have 2 sets of lists one for all the names of things and another with all there values on it
how do I have the names call the values?

[WhiteWolf]

If you go to a search engine and type "excel 2003 drop down lists" you should find a lot of links explaining how to create them. (IMG:style_emoticons/default/biggrin.gif)

Better yet here are two sites for you:

Create a drop-down list

Microsoft's Website on How to Create a Drop Down List


I hope these help you out.

[DamienKnight]

Not sure how much of this you already have, so I will explain as much as I can.


Create a workbook with two sheets. One is the presentation sheet, and one is called something like 'Data'

On the data page, create a list of names in column a, then their corresponding values in columns b, c, d (for however many bits of data the name points to).

For example:

If my table is spell data, column a would have spell names, column b would have type, c would have range, and d would have drain.

My list would look something like this:

Manabolt Mana LOS F/2 +1
Fireball Pysical LOS/Area f/2 +5

etc.

Now select just column a, goto Insert->Names, and name the area you selected "Spell_List"
Then select the entire table, goto Insert->Names and name the entire table "Complete_Spell_Table"

Now go to your presentation sheet. On cell b2 goto Data->Validation and select 'List'
In the list name put '=Spell_List'

Now cell b2 on the presentation page should show a dropdown with spell names in it.

Now go to cell C2, and enter this formula: '=If(B2="","",vlookup(B2,Complete_Spell_Table,2,FALSE))'
Now go to cell D2, and enter this formula: '=If(B2="","",vlookup(B2,Complete_Spell_Table,3,FALSE))'
Now go to cell E2, and enter this formula: '=If(B2="","",vlookup(B2,Complete_Spell_Table,4,FALSE))'


Now whenever you select a spell in the Dropdown in cell B2, the data for the selected spell appears in the following cells.

To explain the formula:

If(B2="",""

This part says, if cell B2 is blank, then this cell should be blank

,vlookup(B2,Complete_Spell_Table,2

this part says, if B2 is NOT blank, then perform a vertical lookup function. Look a the Complete_Spell_Table's first column for the value that is in B2. If the value is found, then display the value from the second column.

,FALSE

This part says,if you dont find the exact value from B2 in the Complete_Spell_Table, then dont guess, just error out.

))

These parenthesis close out the vlookup function, and the if statement.


There you go! Good luck with your spreadsheet. If you need examples, try looking at the armor section of the Gear page in the SR4CG_DK_1 (DL link in my sig). The armor area works much like this example. Also the spell area in the Magic tab, although it only shows drains, no other details.

You may need to goto (Tools->Protection->Unprotect
on the gear page in order to study it.

The list go off of a hidden data page. To unhide it and see the lists, goto Format Sheet Unhide and select 'Gear_Data'