Dropdown List, Dependent on Previous Column's Text

**nickls** · 03-30-2010, 02:56 PM

Hello all,

I'm very new to Excel functions. My issue is two-fold:
1. I need to create a dropdown list
2. I would like that dropdown list to be dependent upon a previous column's text (not necessarily the column right next to it).

Ex: If column W says "return" I would like column Y to display the drop down arrow to choose from my list.

Issues:
I've read about creating drop down lists. I'm using Excel 2007. In a separate sheet I create my list, name the range. I highlight all the cells I want to have the dropdown and go to Data Validation, type the name of the range, but all that happens is the dropdown displays the name of the range. It doesn't show the list of options.

I've searched and read through some of the forums, but havne't come across what I'm looking for yet. I've also done Internet searches but they haven't been quite as helpful.
I also read the forum rules before posting, so if I missed something, please let me know.

Looking forward to learning a whole lot about spreadsheets!

Thanks,
Nick

**Paul** · 03-30-2010, 03:01 PM

When creating the data validation list, don't just type the list name, type =ListName.

Have you read the Dependent Data Validation tips at Contextures?

http://www.contextures.com/xlDataval02.html

**nickls** · 03-30-2010, 03:10 PM

Hi Paul,

Thank you for that tip! I haven't seen that website. I'll give it a look!

**nickls** · 03-31-2010, 02:26 PM

I checked out that site. It definitely helps. I've gotten it to work! One issue I'm having is, I think of new things that need to be added to one or another list, and then I can't rename the list the same name. Is there a way to delete all the list names you've used, or aren't using?

**martindwilson** · 04-01-2010, 05:25 AM

on formula tab click "name manager"

**nickls** · 04-29-2010, 03:29 PM

Thanks for the help on this, all. I've gotten two columns to work, but now I'd like to add a third. I've been reading the link above at contextures.com, but I haven't been able to get it to work.
I'm starting from scratch, create my lists, name them as suggsted, (one word), get the first column working (gives a drop down), but now I can't get the second or third to create a dropdown based on the answer in the first column. If that makes sense.

Column A - dropdown choices are apple and bearhug.
Column B - several choices dependent on "apple" or "bearhug" being selected
Column B doesn't work. It doesn't give me choices.
Colum C is the same. No choices.

=INDIRECT(SUBSTITUTE(B2&C2," ","")) This or =INDIRECT(B2&C2)
are the formulas I'm using in Column B and C

Any help would be most appreciated. Thank you!