+ Reply to Thread
Results 1 to 4 of 4

Excel Dependent Drop Down Error

  1. #1
    Registered User
    Join Date
    07-13-2017
    Location
    California
    MS-Off Ver
    2016
    Posts
    9

    Excel Dependent Drop Down Error

    I am creating a dependent drop down where the drop down itself is located on the first tab of the sheet and the data that includes the dropdown options is on another tab. When the drop down and the data are on different tabs, I get the following error when entering the index match function into the data validation list formula:

    "You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria."

    The formula I am using is the following (which works when it is not in the data validation list formula section):

    =INDEX(sheet4!$A$2:sheet4!$BG$15,,MATCH($A8,sheet4!$A$1:sheet4!$BG$1,0))

    When I move the data I am referencing to the same tab and enter an updated formula to the data validation list section, then I don't get an error.

    This makes me think that the error is occurring because of my "Sheet4!" references in the original formula. Is there a workaround to have a dependent drop down reference data on a separate excel tab?

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,822

    Re: Excel Dependent Drop Down Error

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    07-13-2017
    Location
    California
    MS-Off Ver
    2016
    Posts
    9

    Re: Excel Dependent Drop Down Error

    Thank you! Your edit worked!

  4. #4
    Registered User
    Join Date
    10-22-2024
    Location
    Auckland
    MS-Off Ver
    365
    Posts
    1

    Thumbs up Re: Excel Dependent Drop Down Error

    Thanks! Saved me from going crazy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dependent drop down list excel
    By Theresia2022 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-05-2022, 03:59 AM
  2. Replies: 6
    Last Post: 12-05-2020, 10:46 PM
  3. Error in cascading (dependent) Excel drop down list
    By tigergutt in forum Excel General
    Replies: 7
    Last Post: 02-24-2020, 10:35 AM
  4. [SOLVED] Excel Dependent drop down lists
    By JAKIV in forum Excel General
    Replies: 4
    Last Post: 02-14-2018, 06:38 PM
  5. [SOLVED] Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)
    By Waqar Ali in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-26-2012, 06:31 AM
  6. dependent drop down formula in excel 07
    By malathy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-12-2010, 10:31 AM
  7. Excel dependent drop down forms?
    By dafreedomfita in forum Excel General
    Replies: 2
    Last Post: 08-12-2007, 04:38 AM

Tags for this Thread

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