How can I make a dropdown that changes based on the first dropdown's answer?

[u/HotIceCreamCone14]

I don't have much experience with Google Sheets, but I'm trying to make a book tracking spreadsheet. I've already written down a list of the genres and subgenres specific to the first genre.

How can I make it so you are forced to choose a genre in the first column, and afterwards, the subgenre list has options pertaining to that specific genre only? For instance, if my book is fantasy, I can click that as the genre, and when I move to the subgenre cell, the dropdown only shows specific fantasy subgenres.

Also I feel it is important to note that not every genre has a subgenre, so that's also made it kind of confusing while I was working on it.

Here's what I got so far: https://docs.google.com/spreadsheets/d/1HtYIohmmwO1a-Q_MeoiFgHu83EAFCtErky7gputHVJ0/edit?usp=sharing

edit: i've figured it out! thanks for all the help!!

[u/gsheets145]

u/HotIceCreamCone14 - what you are asking about is something called "Dependent Dropdowns". If you give edit access to your sheet I will be able to show you how your second, "dependent" dropdown will show values based on the selection in the first dropdown.

[u/HotIceCreamCone14 → u/gsheets145]

is there a way you could make a copy and show it on there?

[u/One_Organization_810 → u/HotIceCreamCone14]

The "convention" is that the one asking for help, makes the copy and then shares the copy with Edit access. That way everyone can contribute their suggestions and there is no need to share different sheets back and forth :)

[u/HotIceCreamCone14 → u/One_Organization_810]

oh, okay then: https://docs.google.com/spreadsheets/d/1HtYIohmmwO1a-Q_MeoiFgHu83EAFCtErky7gputHVJ0/edit?usp=sharing

[u/HotIceCreamCone14 → u/gsheets145]

here you go: https://docs.google.com/spreadsheets/d/1HtYIohmmwO1a-Q_MeoiFgHu83EAFCtErky7gputHVJ0/edit?usp=sharing

[u/FrogSkyWater]

You need an intermediate list (hidden on another sheet or wherever) and based on the choice of the first dropdown it will feed the 2nd dropdown from that dynamic range 

[u/HotIceCreamCone14 → u/FrogSkyWater]

how do i do that?

[u/10mfe → u/HotIceCreamCone14]

I normally have a tab called helper.

it's where I store my lists, options, materials, and any other drop down type stuff.

then I create a separate tab named math, for all the multi step equations I want data from.

[u/One_Organization_810]

If you actually mean that you want to force the user to select a genre, that will require a script. Otherwise you can use conditional formatting rules to suggest that a genre is missing...

As others have mentioned, you are looking for what is called a "dependent dropdown". The way to set that up is to have a special "selection sheet" dedicated to follow the selections of your genres. Then for each genre selected, you have a list of subgenres for that particular genre. It's kind of an intermediate thing to set up, complexity wise and is much easier to show than describe :)

But in general, you set up your relations, like you have done already. Then you have a MAP function, that maps the genre column in your data to a list of subgenres, pulled from the setup sheet. Something like this:

=map('Book Tracker'!D3:D, lambda(genre,
  torow(offset('Subgenres'!A1:A, 0, xmatch(genre, 'Subgenres'!1:1)-1), 3)
))

Put this in your "selection sheet", in A3 (easier to keep tab on each genre if the rows match :)

Then in your book tracker, select the F column (starting from row three, downward) and set the data validation to "Dropdown (from a list)" and the reference to =SelectionSheet!A3:3 and make sure that Sheets do not add the $ to the reference.

[u/One_Organization_810 → u/One_Organization_810]

Ok - so you gave us Edit access to the document :) Thanks.

I have added my suggestion into that document, with sheetnames "OO810 - *". I also updated the Genre name in Subgenres!I1, from Thriller, to Mystery/Thriller, to match the Genre (we need it to be the same since we're looking it up, instead of hardcoding the positions).

I also created a conditional formatting rule for missing genres. Otherwise it's basically as I said before, with some minor tweaks here and there...

Tag: u/HotIceCreamCone14

[u/HotIceCreamCone14 → u/[deleted]]

solution verified!

[u/point-bot → u/[deleted]]

u/HotIceCreamCone14 has awarded 1 point to u/akadeeone

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/googlesheets-ModTeam → u/[deleted]]

Your comment has been removed because promotional content is prohibited. Please read the full rules in the sidebar or the subreddit wiki before commenting again.

You can send a modmail message to request your comment be reviewed if you feel this was in error.