Data validation

%3CLINGO-SUB%20id%3D%22lingo-sub-307785%22%20slang%3D%22en-US%22%3EData%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307785%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20add%20a%20dropdown%20list%20that%20at%20the%20same%20time%20exclude%20data%20from%20previous%20rows.%20I'm%20trying%20to%20make%20a%20schedule%20that%20is%20a%20bit%20complicated%20and%20I%20want%20to%20avoid%20selecting%20the%20same%20person%20more%20than%20twice%20in%20the%20same%20row.%20I%20can%20either%20do%20a%20drop%20down%20list%20with%20all%20names%20or%20do%20custom%20data%20validation%20to%20avoid%20entering%20the%20same%20name%20if%20it%20matches%20one%20or%20more%20of%20the%20previous%20cells%20in%20the%20same%20row.%20I%20can't%20do%20both%2C%20and%20I%20wonder%20if%20I%20can%20combine%20both.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20match%20and%20lookup%20functions%20but%20the%20script%20gets%20long%20(as%20I%20am%20trying%20to%20do%20all%20possible%20combinations)%20and%20it%20is%20not%20accepted%20in%20the%20formula%20box%20of%20the%20data%20validation%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20the%20attachement%20for%20more%20details%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-307785%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECustom%20App%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edata%20validation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDropdown%20list%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755902%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259264%22%20target%3D%22_blank%22%3E%40Solom75%3C%2FA%3E%26nbsp%3BSee%3A%20%3CA%20href%3D%22https%3A%2F%2Fwww.contextures.com%2FxlDataVal07.html%23Unique%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.contextures.com%2FxlDataVal07.html%23Unique%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi

 

I want to add a dropdown list that at the same time exclude data from previous rows. I'm trying to make a schedule that is a bit complicated and I want to avoid selecting the same person more than twice in the same row. I can either do a drop down list with all names or do custom data validation to avoid entering the same name if it matches one or more of the previous cells in the same row. I can't do both, and I wonder if I can combine both.

 

I tried using match and lookup functions but the script gets long (as I am trying to do all possible combinations) and it is not accepted in the formula box of the data validation list.

 

Please note the attachement for more details

1 Reply
Highlighted