Forum Discussion
Data Validation for Multi-Column Selection in Drop Down List
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!
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.
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.
XLOOKUP and FILTER and UNIQUE are all new functions, available only in the newest versions of Excel. What version are you using?
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.