Using Drop Down List and custom validation

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


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

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies