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?

 

Ty~

10 Replies

@EVIED1999 

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.

@EVIED1999 

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!

@EVIED1999 

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 

 

EVIED1999_0-1661258300493.png

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

@EVIED1999 

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.

https://docs.google.com/spreadsheets/d/1-UtS1fXZT6clG0-bQZi7-st5KOKMZnAL/edit?usp=sharing&ouid=11556...

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

@EVIED1999 

See the attached version.

You are a wonderful human being!