Drop down lists

Occasional Contributor

Hi guys,


First time posting - relative newbie!


I just wanted to ask how to solve the following:


I would like to have a yes or no drop-down so if someone selects yes the following rows should populate with something like, "item 1", "item 2" and so on. Should the person select no from the drop down, the row below only populates once with "item". 


How would I go about doing this?



10 Replies


See the attached demo. It uses named ranges and an INDIRECT formula.

Absolutely fantastic!! Thank you
Is there a way to then shift cells up if someone toggles "no"? As it stands it leaves blank rows after Item. Sorry if I'm asking too much.


That would require VBA code. Would that be OK?

And do you also want to shift cells down when you select "Yes"?

The way it is currently, when someone replies with the Yes, it populates the next few rows using the method you sent me previously, but when someone replies with no it still leaves the next few rows blank. So it would just be to remove the blank cells anything would help!


We do have to shift down on Yes, otherwise you'd get a #Spill! error since there is no room for the longer list.

See the attached version. You'll have to allow macros.

@Hans Vogelaar 



Based on the doc you sent over with the macro, how would I directly apply this to my sheet?


Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.


I've just saved it in Gdrive - let me know if you can't access it! Thanks again


See the attached version.

You are a wonderful human being!