Ok so I'm working on a character builder for an RPG (NWN).

What I'm trying to do is create a formula the will essentially do the following. I'm open to any solutions that can function.

We have the following information
Lists:
Tiny Weapons
Small Weapons
Medium Weapons
Large weapons
Large Dual Weapons
All weapons

And an output cell based off of another drop down menu (Classes)
Character Size (C3)
Weapon main hand (A13)
Weapon off hand (D13)
Weapon size output (C13)

What if like to have happen

If c3 = large then A13 is a drop down menu of list all weapons.
If c3 = small then A13 is a drop down menu of only weapons from medium small and tiny list

then in cell D13 I need

If C13 = large dual Then D13 = A13, if C13 <> large dual, C3=large d13 = drop down menu of all tiny small and medium weapons and if c3 =small then d13 = drop down list with all tiny and small weapons.


Any help or guidance is appreciated. Been exploring Google and such for days trying to sort this out.

Doing this is quite straight forward.

The first step would be to write out your lists. After doing so you want to give a name to each range of values (Select the range of values, and in the formulas tab, select name manager).

Do this for each of your lists. The names of each of your "dependent" lists should match one of the options in your main list.

Next make the first dropdown with the source of values being your main list.

Now make your second list with the source being "=INDIRECT([cell location of main list])"

INDIRECT will create a reference to the range named by the value of your first field.

Could you simplify a bit more? I'm excruciatingly new to excel and learning from tutorials and such as I go.

I have so far created the named lists in my OP via name manager.

What I'm struggling with is using if statements to decide which list the cell will provide to the user based on values in the other cells.

Like how would you actually write that function in order for it to work?

Just to try something simple. I created a defined name called WeaponsAll. That defined name refers to the name of every available weapon you can choose from.

Then to test if a pull down menu would pro ure I put this formula into a cell on my sheet.

IF(B3="human",(WeaponsAll),0)

I then changed B3 to orc and B3 gives me the 0 I want.
When I change it to human it gives me a random name from the WeaponsAll list. Not a drop down list with selectable options.

What do I do to rectify that?

did you need a template? otherwise maybe you could just create a google.docs and people can point out what you may have done wrong.

managed to do this with the INDIRECT() function.

Created a cell in my data sheet to decide what list was to be used.

=IF('3T Calculations(Manual)'!A13="Double Axe","DoubleAxe",IF('3T Calculations(Manual)'!A13="Two Bladed Sword","TwoBladedSword",IF('3T Calculations(Manual)'!A13="Dire Mace","DireMace",IF('3T Calculations(Manual)'!A13="","",IF(AND('3T Calculations(Manual)'!A36="",'3T Calculations(Manual)'!D3="large",'3T Calculations(Manual)'!C13="melee",'3T Calculations(Manual)'!C17<>"Large Dual",'3T Calculations(Manual)'!C17<>"Large"),'3t Misc'!A42:B42,IF(AND('3T Calculations(Manual)'!D3="small",'3T Calculations(Manual)'!C13="melee",'3T Calculations(Manual)'!A36="",'3T Calculations(Manual)'!C17<>"Large Dual",'3T Calculations(Manual)'!C17<>"Large",'3T Calculations(Manual)'!C17<>"Medium"),'3t Misc'!A43,""))))))

Then used INDIRECT(CellValue) as the source in my DV List

Now i need help again maybe.

i cannot figure out how to have the cell automatically go blank if the selection in another cell is changed.
Any Tips? Thanks!