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
SharePoint Lists - Expanded View Roadmap ID: 57302
Kotus-Tech in SharePoint on
9 Replies
Multi-value fields display no separator
Florian Hein in SharePoint on
1 Replies
Custom profile: hide known file extensions
PatrickF11 in Microsoft Intune on
1 Replies
Excel Text Validation Help
TimothyE in Excel on
5 Replies
SharePoint List Drag item Move error
mvas111 in SharePoint on
0 Replies
SPO list jumps to top when loading new items
ABehrens in SharePoint Developer on
1 Replies