VLOOKUP and Data Validation

  • I have a Data Validation box that needs to produce a drop-down list of options depending on the value of another (dynamic) field. I could do this using nested Ifs, but......the dynamic field can now have one of 12 values (i.e. more than the limit of nested IFs).

    My first thoughts were to use VLOOKUP, i.e. using the name in the dynamic field as the lookup, and then return the name of a Named Range as the result (then Data Validation would get it's list from the Named Range)....but I can't seem to get it to work. My 'code' in the Data Validation Source is as follows (I have selected the List option):


    Where I2 is field containing the dynamic value,
    $E$55:$F$65 contains a column with all the possible dynamic field names plus a matching column of Named Ranges (in the second column).

    When I enter the code above, I get "The source list must be a delimited list, or a reference to a single row or column.

    Any clues? Any other ways of doing this?

  • Re: VLOOKUP and Data Validation

    OK - Its been a long day, I'm tired and I've had a couple of Ales (from near where you are Dave - Little Creatures - second best Australian beer ever)

    It sounds like the VLOOKUP is supposed to return the name of your named range?

    If so try using:



  • Re: VLOOKUP and Data Validation

    Hi Weasel

    I trust that you are well. I have a similar problem with data validation and vlookup. In my workbook I created a simple drop down menu in cell A460 to select your steel section type. From this it should create a drop down list in cell B460 with all the standard sizes of the selected section in A460. My table range is $A$18:$B$441 and I've tried inserting the following formula into a data validation for cell B460: =INDIRECT(VLOOKUP($A460,$A$18:$B$441,2,FALSE)). It returns the following error message: "The source currently evaluates to an error. Do you want to continue?" What am I doing wrong? This is identical to the formula you suggested to bridges_22. Is there a way I can send this file to you as I have tried uploading it to no avail.

    Kind Regards

  • Re: VLOOKUP and Data Validation

    Weasel has not been active on the board for 3 years... ! Did you not notice the thread date??

    Regardless, please don't post questions in threads started by other members. Start your own thread, give it an accurate and concise title as required by the rules here and explain your issue fully.

    If you think another thread can help clarify your issue, you can include a link to it by copying its URL from the address bar of your browser and pasting into your message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!