Excel 2007 : Dynamic Named Range issue with Data Validation

**rarchbol** · 07-29-2010, 02:04 PM

Hi there!

I'm trying to utilize dynamic named ranges for a dependant drop down menu and I can't get it to work.

In a nutshell.......

The main sheet contains 3 drop down menus adjacent to each other, which the 1st is independant and the other 2 are depdandant on its predecessor. Various other cells throughout this sheet autopopulate with values based on the selections of the drop down menus.

The 1st drop down menu utilizes this range for data validation:
=OFFSET('Species List'!$A$5,0,0,COUNTA('Species List'!$A:$A),1)
This effectively retrieves the desired list, and this list adjusts according to new/removed entrys to the list. The list retrieved is a list of animal species, fyi.

The 2nd drop down menu utilizes this range for data validation:
=INDIRECT(B9)
This causes the drop down menu to refer to the value in cell B9 (the 1st drop down menu) and use that as a reference to return the next desired list, which is a list of samples based on the specified Species in the first drop down menu. Both the name of the named ranges (that I want dynamic) and the selections from the B9 drop down menu are identical in terms of characters used.

I have on another sheet, Species List, aside from the list used for the 1st drop down menu, an array of currently named ranges, where each column represents a species and below that header is a list of samples. SO, i'm trying to make these named ranges dynamic with this code:
=OFFSET('Species List'!$C$5,0,0,COUNTA('Species List'!$C:$C),1)
However, with this code utilized, I can't get the 2nd drop down menu to retrieve the dynamic lists.

I know I could just say screw the dynamics and make the named ranges to include space for new entries but I'd really like to keep things as simple and aesthetic as they can be.
I'll attach a copy of the excel file for those who would prefer a first hand look, fyi I have only attempted to adjust the named ranges for Moose and Elk to be dynamic, the rest I have left as normal named ranges as I can't get the dynamics to work.

Any thoughts? Help is mucho appreciated!

Ryan

**DonkeyOte** · 07-29-2010, 02:20 PM

In short.... INDIRECT can't evaluate a Named Range that is itself Dynamic - as yours is.

There are a couple of workarounds ... one quick way is to use the old XLM Evaluate call via another Name, eg:

Please Login or Register  to view this content.

Then modify Sample DV Source to be _DVList2

That said you might want to revisit your dynamic formula for Elk - to discount the non-blanks appearing between C1:C5 or alternatively use a RefersTo formula of:

Please Login or Register  to view this content.

**JBeaucaire** · 07-29-2010, 02:21 PM

You can't INDIRECT() reference a dynamically self-adjusting range, at least I've given up trying after a couple of years at it.

**DonkeyOte** · 07-29-2010, 02:27 PM

@JB - the XLM Evaluate route works with Dynamic Names without issue but does warrant saving as .xlsm in 2007+

The other possibility is to use CHOOSE but has obviously limited practicality.