Dependent List Validation from Dynamic Named Ranges

All,

I have something that in my mind "should" be working, but it is not in Excel 2007. It may be a version issue, but I don't think it is related. I have attached a sample spreadsheet that relates what I am trying to accomplish, but to summarize I am trying to do the following:

• Create Several Dyamically named ranges (using the name manager and the offset function)
• Create a data validation dependent upon first selection
• Force the dependent validation to pick up the correct dynamically named range using a concantenated cell and an INDIRECT function in the list source

However, when I try to do so, I get a pop-up stating that there is an error. The twist is that if I hardcode my named ranges rather than making them dynamic, it works fine. Furthermore, when I hardcode a cell's list validation to the dynamic named range, it works fine. It's only when I try to do a dependent selection that things go awry.

Any help would be greatly appreciated.

I thought I'd update this thread with the workaround version of the spreadsheet...

First of all: what a shame this has to be a system limitation. So far I hadn’t found any limitation using excel, well at least there were reasonable one and I always work around it through VB, but not one like this!

Anyway, I have found an additional workaround without having to write all names on the CHOOSE formula

PROS:
Main categories as well as subcategories will be automatically populated
No need to type in dynamic names on CHOOSE formula
Should be a type and forget about it kind of formula (unless your list has more than 25000, in which case it could be fixed)
There is no need to create dynamic list for ALL subcategories

CONS:
Still need to keep a nice and clean sheet for all categories and might need extra sheet for third dependent list
You need to have one cell to write down the sheet name where the lists are. This is because of another data validation limitation, which is “no other sheet reference”

What I just did is to create a dynamic range for the main categories (on my example called "type") on the entire 1:1 row

Then I formulated directly on the data validation - list field

The formula I used on the dependant list is the following:

=OFFSET(INDIRECT(ADDRESS(1,MATCH(C6,type,0),1,1,E5)),1,0,COUNTA(OFFSET(INDIRECT(ADDRESS(2,MATCH(C6,type,0),1,1,E5)),0,0,25000+N("how much longer are your lists"),1)),1)
C6 is the cell in which the first dynamic list is
E5 contain the name of the cell in which the list are
I wish I could attach the file, but somehow I am unable

You have to delimit the number of rows for the COUNTA formula. If this could bee seen as a disadvantage, it would be an advantage as well, maybe you want to delimitate number of category members so you can have, let’s say, from row 25000 down to other stuff you want to keep.

I still hope someone on Bill's team do the job and fix this problem from its root
If you need something else you can find me here

http://www.allexperts.com/ep/1059-11...o-Castillo.htm

Very cool workarounds...and that last one IS pretty elegant. I've used my previous dozens of times already, and now with this simple solution, I'm going to save A LOT of keystrokes!!

Thanks gang...

good workaround as well!

here is the file I couldn't attach last time

http://www.4shared.com/document/EqMu..._Indirect.html

Thank you so much, I was struggling with this for 3 weeks and you just make it clear and easy to apply, saved my life

sorry to ask but is there any chance you still have the sample file for this that you could send me. thanks