SOLVED

Excel data validation

%3CLINGO-SUB%20id%3D%22lingo-sub-3279769%22%20slang%3D%22en-US%22%3EExcel%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3279769%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all.%20I%20need%20a%20help%20on%20this.%20When%20I%20select%20a%20role%20in%202nd%20table%20(ex%3AA12%20-%20Admin)%20then%20all%20the%20values%20against%20selected%20value%20should%20be%20listed%20in%20a%20drop%20down%20(ex%3Aon%20B12)%20to%20select%20as%20I%20need.%20How%20can%20I%20achieve%20something%20like%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot_20220408_012543.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F362327i5186DFD266D29C7B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot_20220408_012543.jpg%22%20alt%3D%22Screenshot_20220408_012543.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3279769%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3280117%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3280117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928667%22%20target%3D%22_blank%22%3E%40Haveesha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20source%20list%20shouldn't%20have%20blanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3280060%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3280060%22%20slang%3D%22en-US%22%3EAny%20idea%20on%20how%20I%20should%20ignore%20blanks%20in%20the%20list%20itself%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3279983%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3279983%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928667%22%20target%3D%22_blank%22%3E%40Haveesha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20I've%20used%20it%20for%20lists%20with%20thousands%20of%20rows.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3279969%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3279969%22%20slang%3D%22en-US%22%3EBut%20can%20we%20do%20this%20for%20large%20set%20of%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3279961%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3279961%22%20slang%3D%22en-US%22%3EOh%20thank%20you%20so%20much%20for%20this%20solution.%20I%20will%20use%20this%20one.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3279898%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3279898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928667%22%20target%3D%22_blank%22%3E%40Haveesha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESort%20the%20list%20of%20names%2Froles%20by%20Role%20then%20by%20Name.%3C%2FP%3E%0A%3CP%3EFor%20B12%2C%20set%20up%20Data%20Validation%20of%20type%20List%20with%20source%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DOFFSET(%24A%241%2CMATCH(%24A12%2C%24B%242%3A%24B%247%2C0)%2C0%2CCOUNTIF(%24B%242%3A%24B%247%2C%24A12)%2C1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all. I need a help on this. When I select a role in 2nd table (ex:A12 - Admin) then all the values against selected value should be listed in a drop down (ex:on B12) to select as I need. How can I achieve something like this? 

Screenshot_20220408_012543.jpg

6 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Haveesha 

Sort the list of names/roles by Role then by Name.

For B12, set up Data Validation of type List with source

 

=OFFSET($A$1,MATCH($A12,$B$2:$B$7,0),0,COUNTIF($B$2:$B$7,$A12),1)

 

See the attached sample workbook.

Oh thank you so much for this solution. I will use this one.
But can we do this for large set of data?

@Haveesha 

Yes, I've used it for lists with thousands of rows.

Any idea on how I should ignore blanks in the list itself?

@Haveesha 

The source list shouldn't have blanks.