Data Validation for Multi-Column Selection in Drop Down List

%3CLINGO-SUB%20id%3D%22lingo-sub-1892449%22%20slang%3D%22en-US%22%3EData%20Validation%20for%20Multi-Column%20Selection%20in%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1892449%22%20slang%3D%22en-US%22%3E%3CDIV%3EI%20have%20Sheet1%20and%20Sheet2%20tabs%20in%20my%20Excel%20spreadsheet.%3C%2FDIV%3E%3CDIV%3ESheet1%20has%20data%20in%20columns%20A%20%26amp%3B%20B.%3C%2FDIV%3E%3CDIV%3ESheet1%20column-A1%26nbsp%3B%20has%20a%20column%20header%20named%20Food%3C%2FDIV%3E%3CDIV%3ESheet1%20column-B1%26nbsp%3B%20has%20a%20column%20header%20named%20Color%3C%2FDIV%3E%3CDIV%3EThere%20are%20five%20items%20under%20the%20headers%20in%20each%20column%20(Sheet1%20A2%3AA6%2CB2%3AB6).%3C%2FDIV%3E%3CDIV%3EI'd%20like%20to%20create%20a%20data%20validation%20list%20drop%20box%20Sheet2%20A1.%20When%20I%20click%20on%20Sheet2%20A1%2C%20I'd%20like%20to%20see%20the%20data%20list%20showing%20both%20columns%20in%20Sheet1%20(A1%26amp%3B%20B1)%2C%20and%20have%20it%20populate%20Sheet2%20A1%26amp%3BB1%20with%20the%20data%20selected.%3C%2FDIV%3E%3CDIV%3EI'm%20open%20to%20any%20suggestions%20making%20it%20as%20easy%20as%20possible.%3C%2FDIV%3E%3CDIV%3EThank%20you%20in%20advance!%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1892449%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1892553%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20for%20Multi-Column%20Selection%20in%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1892553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823817%22%20target%3D%22_blank%22%3E%40jegarner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20Validation%20doesn't%20work%20that%20way%2C%20so%20it%20would%20require%20VBA%20to%20do%20what%20you%20want.%20That%20seems%20to%20be%20overkill%20to%20me.%3C%2FP%3E%0A%3CP%3EThe%20attached%20version%20shows%20a%20slightly%20different%20way%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0034.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233927iEDBE6DD2E0B941D5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0034.png%22%20alt%3D%22S0034.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20uses%20a%20helper%20column%20on%20the%20data%20sheet%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0035.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233928i528E41AA48001E7E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0035.png%22%20alt%3D%22S0035.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1892558%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20for%20Multi-Column%20Selection%20in%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1892558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823817%22%20target%3D%22_blank%22%3E%40jegarner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20post%20the%20spreadsheet%20as%20it%20exists%20now.%20(just%20make%20sure%20no%20private%20or%20confidential%20info%20is%20in%20it)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20maybe%20describe%20a%20little%20more%20thoroughly%20how%20this%20data%20validation%20process%20is%20to%20work%3A%3C%2FP%3E%3CUL%3E%3CLI%3EIs%20it%20to%20show%20only%20the%20combinations%20in%20each%20row%20of%20A%20and%20B%20in%20Sheet%201%2C%20or%20every%20possible%20combination%3F%3C%2FLI%3E%3CLI%3EIf%20the%20latter%2C%20can%20we%20break%20it%20into%20two%20separate%20steps%20(two%20separate%20entries)%20during%20data%20entry%20in%20Sheet%202%3F%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20describe%20just%20a%20bit%20more%20what%20the%20application%20is%2C%20where%20it's%20to%20be%20used%2C%20how%2C%20by%20whom.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1892618%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20for%20Multi-Column%20Selection%20in%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1892618%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20all%20of%20your%20helpful%20suggestions%2C%20but%20I%20was%20able%20to%20get%20what%20I%20wanted%20to%20do%20with%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIFERROR%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EVLOOKUP%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3ESheet1!A2%3AB%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22boolean%22%3EFALSE%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22Choose%20a%20Food%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1892946%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20for%20Multi-Column%20Selection%20in%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1892946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823817%22%20target%3D%22_blank%22%3E%40jegarner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20certainly%20welcome.%20I'm%20glad%20you%20found%20a%20solution%20that%20is%20working%20for%20the%20time%20being.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20have%20to%20say%2C%20it%20looks%20like%20something%20that%20can%20function%2C%20but%20may%20not%20be%20the%20most%20effective%20in%20the%20long%20run.%20One%20of%20the%20fascinating%20things%20about%20Excel%20is%20that%20there%20often%20are%20multiple%20routes%20to%20a%20workable%20solution.%20Sometimes%20they're%20equally%20effective%3B%20other%20times%20they're%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I'd%20still%20invite%20you%20to%20post%20your%20spreadsheet%20and%20solicit%20feedback.......%20along%20with%20a%20full%20description%20of%20the%20context%20here.%20How%20is%20this%20being%20used%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1896729%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20for%20Multi-Column%20Selection%20in%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1896729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20see%20if%20there%20are%20any%20other%20ways%20to%20make%20this%20multi-column%20drop%20down%20list%20bring%20over%20two%20columns%20of%20data%20using%20a%20Data%20Validation%20List.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20example%20of%20what%20I've%20been%20trying%20to%20accomplish.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20you%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
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 are five items under the headers in each column (Sheet1 A2:A6,B2:B6).
I'd like to create a data validation list drop box Sheet2 A1. When I click on Sheet2 A1, I'd like to see the data list showing both columns in Sheet1 (A1& B1), and have it populate Sheet2 A1&B1 with the data selected.
I'm open to any suggestions making it as easy as possible.
Thank you in advance!
12 Replies

@jegarner 

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:

S0034.png

It uses a helper column on the data sheet:

S0035.png

@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.

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")

@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?

 

@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!

@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.

@mathetes 

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.

 

@jegarner 

 

XLOOKUP and FILTER and UNIQUE are all new functions, available only in the newest versions of Excel. What version are you using?

@mathetes 

 

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.

@jegarner 

 

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.

@mathetes 

 

Thanks for the effort and the suggestions you've provided.