Using Drop Down List and custom validation

%3CLINGO-SUB%20id%3D%22lingo-sub-382916%22%20slang%3D%22en-US%22%3EUsing%20Drop%20Down%20List%20and%20custom%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382916%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20part%20of%20an%20assignment%20handed%20off%20to%20me%20from%20my%20manager%2C%20I%20have%20to%20create%20a%20very%20complex%20excel%20online%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20an%20error%20message%20for%20when%20a%20user%20selects%20a%20name%20that%20is%20on%20the%20previous%20date.%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20%2C%20%22Andrew%20A.%22%20under%20Auditor%20%231%20is%20Cell%20B6%20and%20Auditor%20%232%20is%20Cell%20C6.%20Those%20cell%20scurrently%20contain%20a%20list%20of%20the%20employees%20at%20our%20location.%20I%20need%20to%20also%20validate%20and%20give%20an%20error%20message%20for%20when%20the%20same%20name%20is%20selected%20for%20B6%20and%20C6.%20Please%20also%20assume%20the%20same%20would%20be%20needed%20for%20every%20other%20pairing%20on%20this%20spreadsheet.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-382916%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-382929%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Drop%20Down%20List%20and%20custom%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382929%22%20slang%3D%22en-US%22%3EI%20suggest%20that%20if%20an%20employee%20has%20already%20been%20selected%20as%20under%20a%20specific%20auditor%2C%20that%20same%20employee%20can%20no%20longer%20be%20selected%20as%20under%20another%20auditor.%3CBR%20%2F%3EThereby%2C%20the%20need%20for%20an%20error%20alert%2C%20which%20can%20be%20ignored%20anyway%2C%20will%20be%20avoided.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-382921%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Drop%20Down%20List%20and%20custom%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F306276%22%20target%3D%22_blank%22%3E%40Sigmas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EUsing%20structured%20references%20to%20create%20a%20field%20within%20a%20table%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3D%20IF(%20%5B%40Auditor1%5D%3D%5B%40Auditor2%5D%2C%20%22Error%22%2C%20%22%22%20)%3C%2FP%3E%3CP%3E%3CSPAN%3Egives%20%22Error%22%20or%20blank.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20total%20count%20of%20double%20assignment%20errors%20would%20be%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3D%20SUMPRODUCT(%20SIGN(%20Table1%5BAuditor1%5D%3DTable1%5BAuditor2%5D%20)%20)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

As part of an assignment handed off to me from my manager, I have to create a very complex excel online spreadsheet.

 

I am trying to create an error message for when a user selects a name that is on the previous date.

As you can see , "Andrew A." under Auditor #1 is Cell B6 and Auditor #2 is Cell C6. Those cell scurrently contain a list of the employees at our location. I need to also validate and give an error message for when the same name is selected for B6 and C6. Please also assume the same would be needed for every other pairing on this spreadsheet. 

2 Replies
Highlighted

@Sigmas 

Using structured references to create a field within a table

= IF( [@Auditor1]=[@Auditor2], "Error", "" )

gives "Error" or blank.

The total count of double assignment errors would be

 = SUMPRODUCT( SIGN( Table1[Auditor1]=Table1[Auditor2] ) )

Highlighted
I suggest that if an employee has already been selected as under a specific auditor, that same employee can no longer be selected as under another auditor.
Thereby, the need for an error alert, which can be ignored anyway, will be avoided.