Dynamic Named Ranges and Indirect Data validation INCOMPATIBLE?

I can use a dynamic named range and a direct reference to the name and the DV works.

I can use a standard named range (not dynamic) and an INDIRECT() reference to a cell with the name of the range in it and the DV works.

But if I try to use a dynamic named range and an indirect reference to a cell with the name of the range in it, the DV fails.

Any workarounds out there? This project includes a LOT of DV lists, getting them to self-maintain is important if I can do it.

(Cross-posted)

some info here
http://www.dailydoseofexcel.com/arch...ynamic-ranges/
and some other ideas here
http://www.mrexcel.com/forum/showthread.php?t=315470

Both of those threads appear to simply confirm the problem, not any usable suggestions.

I think both threads (c/o Aladin & Martin Kral (MS XL MVP)) make reference to the possibility of CHOOSE ...

Using your file if you were to create a named range containing your list names, eg:

Name: _List
RefersTo: {"Colors","Colors2"} ... or range if preferred / easier

Then

Please Login or Register  to view this content.

Now this may not be viable for you pending no. of named ranges in play given CHOOSE limitations but it is a workaround albeit perhaps not the most elegant...

It's a custom ordering sheet where each "line" of products has a drop down box of customization options for that line. We've only finished the first of 12 categories and we're already at 15 different option lists.

The option lists all have to appear on the ordering sheet in the same cell, thus we've added a "category" column to the product database and when a code is entered on the order form, it populates the dropdown with the "list" matching that category. We do this currently with an =INDIRECT($M14) in the data validation cell for that row.

The problem I'm trying to resolve for them: The lists will change fairly frequently. It's a big win if the girl maintaining the order sheet only needs to add/delete items from the lists and the lists adjust themselves to match. Right now, they have to manually adjust the named ranges as specific references, too.

=========
Perhaps I'm just being dense. I've looked at the CHOOSE command and I realize I can use it in a cell, but the solution I need has to work as a Data Validation criteria.

As it stands, I'm not positive I can restructure this to keep the # of possible lists under 30. But trying a smaller version of what the final formula would be, I got this:

=CHOOSE(MATCH($M14,$AP$8:$AP$37,0),List5,List1,List11,List10,List15,List4)

But I get an error when I try to put that into a Data Validation in column C. Have I missed something for using CHOOSE() in the DV part of this?

you dont have to worry about 29 limit coz it should work with nested choose
=IF(A1>29,CHOOSE(A1-29,"martin30","martin32","martin33"),CHOOSE(A1,"martin1","martin2","martin3",...."martin29"))
as per your formula it works ok with the small example your attached spread sheet

Ah, thanks Martin and Don, I understand what I did wrong originally. Yes, I can see how this would solve the original problem.

I would have no problem using this, but the goal, better phrased, is "to make it easier on the non-Excel-savvy girl who will maintain the lists".

Editing all the drop box formulas on the invoice is not her job, so this would results in more confusion, plus getting into this CHOOSE formula two-nested levels deep results in more, PLUS maintaining another list of lists, ...this wouldn't be less work for her, it would be more, and more confusing.

However, now I know how *I* can get around this limitation on my own stuff, so for that, thanks very much!

Originally Posted by Palmetto

Would you please provide/explain the details of how you're getting around the limitation? Perhaps we could benefit as well. Thanks.

I'm referring to the solution described in Martin's post #6. It works. It's a lot more work and requires customizing the DV formula each time you add a new list, but it works.

Fine by me...post away.

Yes, that's the solution offered by Don and Martin. So, we're three for three on the "Choose" Option.

In the end, this would be 40-50 named ranges. Those CHOOSE formulas would be cumbersome beyond manageability for anyone except us. For this project, I've had to simply revert to "bigger than needed" flat ranges that the secretary can simply add to/delete from and sort to her hearts content.

@ css0911

Welcome to the forum.

Unfortunately you need to start a new thread for this.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.