Google Sheets Drop Down Help

[swampz]

I manage a google spreadsheet for a video game I follow that contains lists of different items you can get in the game.

I'll try to explain this next part to the best of my ability.

With these items, I created a "Build Maker" which uses the items to allow people to put together a build that they would want to do in the video game itself.

I have done this by using drop-down lists and VLOOKUP formulas. This part works great, however, I want it so that when you change the class, it changes the data in the drop-down lists to the ones from other sheets. Link for video for better explanation (can't post link): https://imgur.com/a/YCnQCtV

I'm not a google sheets pro and I do not code/program so if someone could explain in detail how I could make this work, it would be very much appreciated.

[janmorris]

as the data for each class has different items to be selected, it is not possible for google sheets to know what to update to so theres no way it can just "automatically update". you will need to make new selections.

Depending on the spreadsheet, it may be possible to use dependent dropdown lists which will allow you to select the data according to the correct class.

Are you able to share a sample (duplicate) link (with editor access) of the spreadsheet file?

[swampz]

Hi, thanks for your response. Yeah, I figured it had something to do with dependant drop-down lists, I watched a couple of videos on them but got a tad confused haha. I've made a copy, which should be editable and shortened the link so I can post it - shorturl(dot)at(slash)fkC69

[janmorris]

Ok heres a step by step to get the dynamic dropdowns....

create a new tab, and name it Items (with a capital "I")

on the new Items sheet, do the following:

put this formula into cell A1:

Please Login or Register  to view this content.

Enter the character names (Pygmalien, Vanessa, Mak, Jules, Dooley, Stelle) into individual cells B1:G1

enter this formula into cell B2:

Please Login or Register  to view this content.

then drag it across to cell G2

Create a named range on the Items sheet, for the range of:

Please Login or Register  to view this content.

call this range items (with a lower case "i")

change to the Build Maker (WIP) tab

click on cell C2

on the menu, go to Data > Data validation

ensure it is "List from a range"

enter the formula:

Please Login or Register  to view this content.

click "Save"

drag cell C2 all the way across to cell L2

click on cell C3

enter this formula

Please Login or Register  to view this content.

drag C3 down to cell C9
drag C3:C9 across to L3:L9

OK.. DONE!


Now... select a class in cell A2, then you will see the dropdowns will allow you to select new item according to the Class

[swampz]

Ahh, thank you so so much! Got it working! This is epic.

[janmorris]

thats awesome news!

i noticed in 'Pygmalien'!A104 that you have "None", but for other class you do not have this.

anyhow, this is why we created range to row 200, so that you have lots of available rows to play with. Google Sheets data validation will automatically remove duplicates and blanks, so this is not a problem

so, if this answers your problem completely, please click on [Thread Tools] above your first enquiry, then select [Solved]. This helps others to know that the problem has a matching solution that may help them too.

[swampz]

Haha yeah, I added "None" to the other classes as well. I also added a reset button script to clear the options more easily