# Indirect Data Validation of Dynamic Named Ranges

1. ## Indirect Data Validation of Dynamic Named Ranges

I'm trying to create an indirect data validation of dynamic named ranges of employees and their locations that can be updated as I download new employee lists from a payroll list that can change. Basically I want to to be able to select the location "NY" and have only the employees in NY pull up in a separate drop down field.

I can get the dynamic named ranges to work using the below formula to name each location (ex. NY) but when I try to do an indirect formula in the data validation it says that Excel evaluates an error. I never have this problem with a normal indirect data validation for named ranges but for some reason this dynamic named range is causing me problems.

=INDEX(Employees!\$B:\$B,MATCH("NY",Employees!\$A:\$A,0)):INDEX(Employees!\$F:\$F,MATCH("NY",Employees!\$A:\$A,1))

A B
NY Employee 1
NY Employee 2
NY Employee 3
NY Employee 4
PA Employee 5
PA Employee 6
PA Employee 7

2. ## Re: Indirect Data Validation of Dynamic Named Ranges

Is \$F:\$F in the formula correct?

I think the problem here is Excel doesn't like using INDRECT to create dependent drop down lists when the named ranges involved are dynamic.

There are ways around it such as using tables to drive the dynamic named ranges but that may not be suitable depending on how your employee list is built/maintained.

BSB

3. ## Re: Indirect Data Validation of Dynamic Named Ranges

I thought that might be the issue. I don't have an issue using tables as it's a pretty easy list to download and paste into excel...just never done that before.

4. ## Re: Indirect Data Validation of Dynamic Named Ranges

Simply a case of creating a table for each State(?) that was in column A and adding the names from column B to the relevant one.
Then create a named range and rather than use the formula above to drive the range, select the names in the table and it'll show something like =NYTable[Names] or =PATable[Names]

Should work fine then.

BSB

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1