Nov 16 2020 01:07 PM - last edited on Nov 09 2023 11:09 AM by
Nov 16 2020 01:24 PM
Data Validation doesn't work that way, so it would require VBA to do what you want. That seems to be overkill to me.
The attached version shows a slightly different way:
It uses a helper column on the data sheet:
Nov 16 2020 01:30 PM
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:
Maybe describe just a bit more what the application is, where it's to be used, how, by whom.
Nov 16 2020 02:13 PM
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")
Nov 16 2020 05:05 PM
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?
Nov 17 2020 08:58 AM
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!
Nov 17 2020 09:17 AM
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.
Nov 17 2020 11:01 AM
The spreadsheet data is only an example to simplify things.
The actual data is hiking/backpacking gear and consumables in Column A, and the weight of items in Column B.
I use two sheet tabs. One for days hikes and one for backpacking. I also have another sheet tab for all gear and consumables, where I get the data from.
The items I bring for either hiking or backpacking vary with the change of seasons. I also am very critical to bring the minimum amount of weight required for a safe trip, so I list and go over weights thoroughly.
I have a considerable amount of experience, so I know what to bring. I'd just like to select items as needed from a drop down list that would bring the weights over as well and have the totals auto-populate.
I did try my revised spreadsheet with the XLOOKUP. However, when I tried using the drop down list, the data in Column B changed to #NAME?
I also tried the other spreadsheet you attached, but the drop down menu with the names wouldn't open or show anything so that I could select one of them.
Nov 17 2020 11:18 AM
XLOOKUP and FILTER and UNIQUE are all new functions, available only in the newest versions of Excel. What version are you using?
Nov 17 2020 11:27 AM
Office 2016.
I also use my android phone/chromebook to edit the spreadsheet at times so I'm looking for a solution that works with Google Sheets as well.
I was originally hoping to see both columns side by side in the drop down menu, but I can include the weight in with the description to solve that issue.
My main concern is to bring over the weight of items into Column B so that Excel and/or Sheets provides me the totals automatically.
Nov 17 2020 11:54 AM
Office 2016 is the problem then. To use the solutions I gave you, you'll have to update to the most recent versions. And they probably won't work anyway with android on the phone....... sorry. Puts you back where you were.
Nov 17 2020 11:59 AM
Nov 17 2020 12:03 PM