Excel multi-word list items possible?

January 21, 2011 12:01 PM

Excel question: Is there any way to have multi-word options (without underscores) in a drop-down list that will be used as a condition for a second drop-down list?

For example, List A consists of "Fruits", "Veggies", and "Meat". If the user selects Fruits, Excel goes to a list that I have named "Fruits" and displays those items, say "Apple", "Pear", and "Orange".

But what if I want an item in List A to have multiple words, say "Fruits and Veggies"? In that case, there would be no way to connect it to a list, because names in Excel can't have spaces. I know that I could call it "Fruits_and_Veggies", but I'd like to avoid that if at all possible. Is there any way to do this?

I'm using Excel 2003 for Windows, BTW.

posted by Awkward Philip to Technology (2 answers total)

I've never encountered the issue you raise about no spaces in names. I have spaces in excel text fields all the time. Am I missing something?

Here's a page that gives a VBA solution to your question about conditional combo boxes. Does that look like what you're trying to accomplish?
posted by jasper411 at 1:15 PM on January 21, 2011

Best answer: Yes - it is possible. In the formula for the secondary list's data validation, you can just use the REPLACE function to strip out the spaces. Your secondary list named ranges would be without spaces. For example, if your first list is in A1, this is what you'd put in for data validation for the dependent list:

=INDIRECT(REPLACE(A1, SEARCH(" ",A1),1,""))

So even if the result of the first list was "Fruit and Veggies", this would strip it down to FruitandVeggies, which would be the named range for the second list.
posted by beyond_pink at 5:25 PM on January 21, 2011