Forum Discussion

jegarner's avatar
jegarner
Copper Contributor
Nov 16, 2020

Data Validation for Multi-Column Selection in Drop Down List

I have Sheet1 and Sheet2 tabs in my Excel spreadsheet. Sheet1 has data in columns A & B. Sheet1 column-A1  has a column header named Food Sheet1 column-B1  has a column header named Color There a...
excel
mathetes's avatar
mathetes
Silver Contributor
Nov 16, 2020

jegarner 

 

Can you post the spreadsheet as it exists now. (just make sure no private or confidential info is in it)

 

And maybe describe a little more thoroughly how this data validation process is to work:

  • Is it to show only the combinations in each row of A and B in Sheet 1, or every possible combination?
  • If the latter, can we break it into two separate steps (two separate entries) during data entry in Sheet 2?

 

Maybe describe just a bit more what the application is, where it's to be used, how, by whom.

jegarner's avatar
jegarner
Copper Contributor
Nov 16, 2020

Thanks for all of your helpful suggestions, but I was able to get what I wanted to do with the following formula:

=IFERROR(VLOOKUP(A2,Sheet1!A2:B,2,FALSE),"Choose a Food")

  • mathetes's avatar
    mathetes
    Silver Contributor
    Nov 17, 2020

    jegarner 

    You're certainly welcome. I'm glad you found a solution that is working for the time being.

     

    I'd have to say, it looks like something that can function, but may not be the most effective in the long run. One of the fascinating things about Excel is that there often are multiple routes to a workable solution. Sometimes they're equally effective; other times they're not.

     

    So I'd still invite you to post your spreadsheet and solicit feedback....... along with a full description of the context here. How is this being used?

     

    • jegarner's avatar
      jegarner
      Copper Contributor
      Nov 17, 2020

      mathetes 

      I'd like to see if there are any other ways to make this multi-column drop down list bring over two columns of data using a Data Validation List. 

       

      I've attached the example of what I've been trying to accomplish.

       

      Thanks for you help!

      • mathetes's avatar
        mathetes
        Silver Contributor
        Nov 17, 2020

        jegarner 

         

        Here's another formula for your Results sheet. Enter this in Cell B2 and copy it down...

        =XLOOKUP(A2,Sheet1!$A$2:$A$7,Sheet1!$B$2:$B$7,"Error",0)

         

        However, you still haven't explained the full context here. I suspect that you're not actually dealing with fruits and vegetables (or is Avocado actually a fruit?)...anyway, maybe the actual application is confidential, so fine.

         

        The XLOOKUP function is relatively new, and more flexible/powerful than VLOOKUP and the other associated LOOKUPs from long ago.

         

        I have a spreadsheet that creates a secondary dropdown that changes based on what's entered in the first column. There's a dependency, in other words. I don't think that's what you're seeking--doesn't seem to be what you've described--but I'll attach a generic example of that.

         

        The functions in the DataValCascading do require the most recent release of Excel, by the way.

Resources