Trying to create a formula that will display results from a table based on responses from 2 dropdown boxes

user_19177675 0
Viewed 52

I have 2 dropdown boxes. Dropdown 1 has 11 possible choices. Dropdown 2 has 20 choices. Dropdown 1 will tell which table/range to pull numbers from. Dropdown 2 will determine which set of numbers from the table/range to display. I already have the 2 dropdowns working. What I am trying to figure out now is how to have a set of 5 cells to display the numbers based on the choices made in the dropdowns. If it helps to describe the usage, it is for a Dungeons and Dragons form for saving throws. The class(dropdown 1) tells which set of numbers to look in, the level of the character(dropdown 2) tells which specific 5 numbers to display as the numbers lower as your level increases. Class and Level selected by user

Cells to list Saving Throws

What I want is to display the correct scores in cells D15:D19 based on the class and level of the character. Saving Throw table

I think I am close. The lines below should individually check to see if the class(J8) matches and if a level(J9) was selected, and if so, print the correct stat in D15. D15 should remain blank until both of the dropdowns are selected. I just need to figure out how to combine all the lines into one formula in cell D15.

IF(J8="Fighter", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A22:B41,2))

IF(J8="Paladin", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A22:B41,2))

IF(J8="Ranger", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A22:B41,2))

IF(J8="Cavalier", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A22:B41,2))

IF(J8="Druid", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A68:B87,2))

IF(J8="Cleric", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A68:B87,2))

IF(J8="Mage", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A45:B64,2))

IF(J8="FighterMage", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A114:B133,2))

IF(J8="Thief", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A91:B110,2))

IF(J8="Rogue", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A91:B110,2))

IF(J8="Bard", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A91:B110,2))

The line below functions to check for "Fighter", check the Level and print the number in D15 and leave it blank if "Fighter" is not selected, but for some reason it only gets the correct number if level 1,2,18,19 or 20 is selected. I don't understand why as all the numbers are in the range chart as shown in the image above. This is only to see if I can get any part to work. Obviously I need it to check for not just "Fighter", but each class.

=IF(J8="Fighter", IF(ISBLANK(J9),"",VLOOKUP(J9,'D&D Stats'!A22:B41,2)),IF(ISBLANK(J9),"",""))

google-sheets-formula
1 Answers

You'd be better off unpivoting your table and then using QUERY to break it all down. If you shift everything over to the right one column and fill A with the class, you can do this

=ARRAYFORMULA(
  IF(COUNTA(B2:B4)<>3,,
   IFERROR(
    QUERY(
     SPLIT(
      FLATTEN(data!A2:A&"|"&data!C1:G1&"|"&data!B2:B&"|"&data!C2:G),
      "|",TRUE,TRUE),
     "select Col4
      where
       Col4 is not null and
       Col1 = '"&B2&"' and
       Col2 = '"&B3&"' and
       Col3 = "&B4&""))))

This assumes you have the following:

  • B2 = Class
  • B3 = Weapons (poison, rod, breath, spell, etc)
  • B4 = Level

You can modify it to suit your needs, but it should work nicely.

user_122540980
Related