+ Reply to Thread
Results 1 to 4 of 4

Indirect Data Validation of Dynamic Named Ranges

  1. #1
    Registered User
    Join Date
    12-07-2018
    Location
    Denver
    MS-Off Ver
    10
    Posts
    2

    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. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010 & 2016
    Posts
    4,534

    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
    Last edited by BadlySpelledBuoy; 12-07-2018 at 12:09 PM.
    Please show appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    12-07-2018
    Location
    Denver
    MS-Off Ver
    10
    Posts
    2

    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. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010 & 2016
    Posts
    4,534

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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