Data Validation and Dynamic Named Range Lists

I have a spreadsheet I have created for recording daily food allowances for a popular diet plan. Recently, I decided to fix it up a bit as the food list I was using under the data validation was getting to long and unruly. I split the food list into logically separate lists on different worksheets and gave each list it's own range name from "A" to "J", excluding "C" which cannot be used (it's reserved).

A=Fruits & Veggies
B=Breads, Cereals, Starchy Veg
D=Meats, Poultry & Fish
etc...

In column B, I select from a pulldown menu a letter between A and J which represensts the category and also is the actual named range.

In column C, I want to be able to use the Data Validation to limit the pulldown list to the appropriate named range associated with the letter in column B. For instance, if the user has selected "J" for Dairy in Column B, I want the pull down list in column C to show only the items from the named range "J".

In column D I use the Vlookup to lookup the contents of column C and find the number of points associated to that food. That works find using the INDIRECT(B3) function in place of the actual named range in the Vlookup forumla.

However, back to the Data Validation List in Column C. I have tried using the =INDIRECT(B3) in the "source" for the list, but it won't allow me to. If I use =INDIRECT("B3"), my list is confined to the letter in column B (ie. "J") but it does not associate that with a named range and provide me with that list of items in the named range.

Any suggestions?

Trish

Try this link.....it does just what you want

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

Funny, that's where I got the idea in the first place. I have essentially done the same thing, but instead of creating a named range for the list A-J, I just listed it in the data validation (they created a "Produce List" and named it Produce). The second part I did EXACTLY the same thing, and my results were an error. Bascially the error says I have to reference a single cell or a range of cells (named range, as well, since I know that works).

Perhaps it a version issue. This issue I am having is on my work computer running a version of Excel that's a couple of years old. I will try on my home computer with Excel 2007 and see if I have better luck.

Trish

Works just fine on my home computer. Oh well. Thanks!

Trish

I finally got the INDIRECT function to work after opening my 2003 workbook in 2007, and after deleting and recreating all named ranges. I am not convinced it's a version issue, but will try and save it in a lesser version, take it to work tomorrow and see if it will work under 2003.

So, my new problem is that the named range I reference in another cell using the INDIRECT function under the Data Validation List souce, has multiple columns. So the pull-down menu generated by the Data Validation shows all the data in all three columns. How do I get it to list only the items in the first column of the named range for data validation.

I use that same INDIRECT function in a forumla for a VLOOKUP, so I need all the columns for that named range. Apart from creating a second named range (one for the entire table of three columns and one for the first column) is there any other way to do this? It would be sloppy and I would prefer to only have the one named range, particularly as I do the INDIRECT VLOOKUP using the same source cell as the Data Validation List source.

Trish

Got in working in 2003. I guess in 2007, it allowed a Named Range with multiple columns to be used in the List Source Field of Data Validation, but not so in 2003. Had to be a single column Named Range. In 2007, I couldn't find it to only List the items in the first column of then Named Range using INDIRECT, so it was displaying ALL of the data from all rows and columns -- not what I wanted.

So, had to create a second range for the first column only. To accomodate using the same source "name" in cell B3 (ie "Fruit_Veg") which happens to be the Named Range I use in the Vlookup in another cell, I created a one column range called "Fruit_Veg_1" and then in the Data Validation, I used the following in the List Source field

=INDIRECT(B3&"_1").

It works fine, and allows the user to only have to pick the category once, but I still had to create a second named range, so it's not as clean as I had hoped.

Thanks, Trish