jbeaucaire
Well-known Member
- Joined
- May 8, 2002
- Messages
- 6,012
This works:
1) INDIRECT() data validation and flat named ranges
2) Direct data validation and dynamic named ranges
This doesn't work:
3) INDIRECT() data validation and dynamic named ranges
Any magic wands out there on this? I could really use some way get #3 working in some form without VBA.
I played around with CHOOSE(), but not only couldn't get it to work as a Data Validation formula, I'm not sure it's appropriate since the # of lists will probably exceed 29.
=============
Unnecessary background:
I have an ordering sheet with a growing number of "option lists".
1) The product lookup table includes a column where we designate the correct options list to use.
2) On the ordering sheet, these lists populate themselves into a drop box in column C based on the product chosen in column A. This is done via an INDIRECT() Data Validation formula that looks like this:
=INDIRECT(INDEX(OptionLists,MATCH($A12,Codes,0)))
3) The lists themselves are maintained on another sheet. We have 12 product lines to define option lists for, and after completing only 1 product line, we're already up to 15 lists. Not sure, but I believe the final number of option lists will number 40-50.
4) Each list, once completed, is given a flat Named Range matching the Option List name designated in the product lookup table.
Everything is working fine up to here.
GOAL: Be able to convert the flat named ranges into Dynamic Named Ranges so the lists update themselves as the girl adds/deletes items from any of the lists.
PROBLEM: When I change an option list from a flat named range to a dynamic named range, the INDIRECT() formula above stops working for that list. It's as if the list doesn't exist.
The dynamic name range formula I'm using is:
=INDEX(Options!$D:$D, 2):INDEX(Options!$D:$D, COUNTIF(Options!$D:$D,">"""))
(crosspost and simple sample sheet)
1) INDIRECT() data validation and flat named ranges
2) Direct data validation and dynamic named ranges
This doesn't work:
3) INDIRECT() data validation and dynamic named ranges
Any magic wands out there on this? I could really use some way get #3 working in some form without VBA.
I played around with CHOOSE(), but not only couldn't get it to work as a Data Validation formula, I'm not sure it's appropriate since the # of lists will probably exceed 29.
=============
Unnecessary background:
I have an ordering sheet with a growing number of "option lists".
1) The product lookup table includes a column where we designate the correct options list to use.
2) On the ordering sheet, these lists populate themselves into a drop box in column C based on the product chosen in column A. This is done via an INDIRECT() Data Validation formula that looks like this:
=INDIRECT(INDEX(OptionLists,MATCH($A12,Codes,0)))
3) The lists themselves are maintained on another sheet. We have 12 product lines to define option lists for, and after completing only 1 product line, we're already up to 15 lists. Not sure, but I believe the final number of option lists will number 40-50.
4) Each list, once completed, is given a flat Named Range matching the Option List name designated in the product lookup table.
Everything is working fine up to here.
GOAL: Be able to convert the flat named ranges into Dynamic Named Ranges so the lists update themselves as the girl adds/deletes items from any of the lists.
PROBLEM: When I change an option list from a flat named range to a dynamic named range, the INDIRECT() formula above stops working for that list. It's as if the list doesn't exist.
The dynamic name range formula I'm using is:
=INDEX(Options!$D:$D, 2):INDEX(Options!$D:$D, COUNTIF(Options!$D:$D,">"""))
(crosspost and simple sample sheet)
Last edited:
