Conditional Data being used for data validation input.

%3CLINGO-SUB%20id%3D%22lingo-sub-1642462%22%20slang%3D%22en-US%22%3EConditional%20Data%20being%20used%20for%20data%20validation%20input.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1642462%22%20slang%3D%22en-US%22%3E%3CP%3EI%20may%20be%20approaching%20this%20the%20wrong%20way%2C%20but%20I%20am%20looking%20for%20a%20pointer%20as%20how%20to%20use%20a%20single%20excel%20data%20table%20to%20populate%20a%20series%20of%20data%20validation%20drop%20down%20lists%20in%20a%20separate%20sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20have%20a%20dynamic%20table%20that%20is%20representing%20a%20player%2C%20team%20and%20a%20detail%20reference%20eg%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22406%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22232%22%20height%3D%2225%22%3EPlayer%3C%2FTD%3E%3CTD%20width%3D%2287%22%3ETeam%3C%2FTD%3E%3CTD%20width%3D%2287%22%3ERef%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2225%22%3ESam%20Jones%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2225%22%3EWill%20Smith%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2225%22%3EAdam%20Brown%3C%2FTD%3E%3CTD%3EOrange%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2225%22%3EJohn%20Smith%3C%2FTD%3E%3CTD%3EBlue%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2225%22%3EDan%20Mills%3C%2FTD%3E%3CTD%3EOrange%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eelsewhere%20on%20a%20%26nbsp%3Bseparate%20sheet%20I%20would%20like%20a%20drop%20down%20list%20input%20to%20select%20the%20team%2C%20ie%20it%20would%20just%20then%20show%20the%20option%20of%20Red%2C%20Orange%20and%20Blue.%20%26nbsp%3B%20After%20that%20I%20would%20like%20a%20second%20drop%20down%20that%20would%20allow%20to%20select%20the%20player%20in%20the%20team.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeg%20if%20Red%20was%20selected%2C%20The%20next%20drop%20down%20would%20present%20both%20Sam%20Jones%20and%20Will%20Smith%2C%20or%20Blue%20just%20John%20Smith.%20%26nbsp%3BLastly%2C%20based%20on%20that%20input%20in%20a%20third%20cell%20it%20would%20display%20the%20%22players%22%20details%20for%20that%20combination%20of%20input%20to%20get%20a%20unique%20value%20on%20the%20separate%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1642462%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1642592%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Data%20being%20used%20for%20data%20validation%20input.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1642592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F784899%22%20target%3D%22_blank%22%3E%40mybrrh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20an%20example%20workbook%20I%20created%20for%20similar%20purposes.%20You%20should%20be%20able%20to%20copy%20the%20concepts%20from%20this.%20Come%20back%20with%20questions%20if%20you%20have%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643278%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Data%20being%20used%20for%20data%20validation%20input.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643278%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%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20kind%20sir.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...Of%20course%20it%20is%20just%20my%20luck%20I%20am%20using%20a%20Mac%20so%20I%20am%20running%20Office%202019%20for%20Mac%2C%20so%20the%20SORT%2C%20UNIQUE%20and%20FILTER%20functions%20don't%20exist%20for%20me...%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20the%20same%2C%20it%20helped%20and%20has%20given%20me%20some%20pointers...%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I may be approaching this the wrong way, but I am looking for a pointer as how to use a single excel data table to populate a series of data validation drop down lists in a separate sheet

 

For example, I have a dynamic table that is representing a player, team and a detail reference eg:

 

PlayerTeamRef
Sam JonesRed1
Will SmithRed3
Adam BrownOrange1
John SmithBlue3
Dan MillsOrange2

 

elsewhere on a  separate sheet I would like a drop down list input to select the team, ie it would just then show the option of Red, Orange and Blue.   After that I would like a second drop down that would allow to select the player in the team.

 

eg if Red was selected, The next drop down would present both Sam Jones and Will Smith, or Blue just John Smith.  Lastly, based on that input in a third cell it would display the "players" details for that combination of input to get a unique value on the separate sheet.

 

Thanks

4 Replies
Highlighted

@mybrrh 

 

Attached is an example workbook I created for similar purposes. You should be able to copy the concepts from this. Come back with questions if you have them.

Highlighted

@mathetes 

Thank you kind sir.  

 

...Of course it is just my luck I am using a Mac so I am running Office 2019 for Mac, so the SORT, UNIQUE and FILTER functions don't exist for me...  

 

All the same, it helped and has given me some pointers...  

Highlighted

@mybrrh 

 

FWIW, I'm on a Mac as well. But I do have the Microsoft 365 Subscription service going. I'd recommend it. These new functions alone are worth it.

mathetes_0-1599564436281.png

 

Highlighted

@mathetes 

 

I actually found another cheat workaround by using a couple of Pivot tables to organise the data for me.  It was not perfect but close enough.

 

Thanks for your help...