CODEWITHSUNDEEP
google-sheets
John
John

Reputation: 11

Have results of one dropdown menu influence the next dropdown (and the data attached to each item)

Apologies if the question is a bit confusing, allow me to explain more in detail.

I'm creating a spreadsheet that will help me to compare different weapon performances against certain monsters in a videogame. This would compare different types of bows, crossbows, and other types of ranged weapons.

Bows use arrows, while crossbows use crossbow bolts. Both bows and crossbows have arrows and bolts made of the same metals, but something like a steel arrow has different stats than a steel crossbow bolt.

Thus, I have made a list of "Bows" , "Crossbows" , "Arrows" , and "Bolts"

The bows and crossbows matrices are both arranged by weapon name and the attack bonus associated with it

| Weapon 1 | Weapon 1 Stats |
| Weapon 2 | Weapon 2 Stats |
|   ...    |      ...       |

The ammunition matrices are both arranged as such:

| Bronze Arrow/Bolt | Arrow/Bolt Stats |
| Iron Arrow/Bolt   | Arrow/Bolt Stats |
|      ...          |        ...       |

So essentially what I want to do is have a dropdown that allows you to select from both bows and crossbows alike, and when a selection is made, the second dropdown will only show the correct ammunition type available (arrows for bows, bolts for crossbows).

Then I want to perform a calculation for said Bow/Crossbow selection across every single iteration of the proper ammunition. For example, if I choose an "Oak Bow" , I'd like to perform a Damage Per Second (DPS) calculation using the stats of the Oak Bow and every iteration of arrow (bronze, iron, steel, ... )

I then would plot those DPS values on a chart, and allow myself to add other combinations for a visual comparison.

I do know that this would be easier in a program like MATLAB but I wanted to be able to share this around very easily with my friends.

Upvotes: 0

Views: 44

Answers (1)

Oleg_S
Oleg_S

Reputation: 161

See testFile.

Sheet "Data" contains tables Weapons(Name, Ammunition, Stats), Arrows(Name, Stats) and Bolts(Name, Stats). For tables there respective named ranges.

Named ranges

Also in columns K and L there are support for ammunition filter based on selected weapon in sheet "Filter".

=QUERY({INDIRECT(VLOOKUP(Filter!A2,Data!$A$2:$B,2,0))},"Select Col1",0)

Sheet "Filter" has Data validation with dropdown list of weapons, when one selected dropdown list of ammunition updates respectively.

Also weapon stats are lookuped with following formula

=ARRAYFORMULA(
IF(A2:A="","",
VLOOKUP(A2:A,Weapons,3,0)))

and munition stats

=IF(C2="","",
VLOOKUP(C2,
INDIRECT(VLOOKUP(Filter!A2,Data!$A$2:$B,2,0)),2,0))

You can add more filters by adding in "Data" support for ammunition filter and setting Data validation to respective range.

Upvotes: 1

Related Questions