Data Validity and Invalid Value Error

[Jason_Stetler]

Are we able to use IFS() statements to suggest multiple invalid value errors for multiple scenarios? I haven’t been able to pull it off and I’m not sure if it is possible.

Thanks!

[Suvrutt_Gurjar]

If you elaborate further, one could suggest on the feasibility.

[Jason_Stetler]

I am using the Google Calendar function right now and trying to limit the data validity for the [Attendees] column.

I’ve written formula to throw an error if the attendees selected are scheduled elsewhere during the same time period, and also want to limit the attendees chosen to 3.

I tried using an IFS() statement to say “Attendee is unavailable for time selected” in the first scenario, and also “Please limit attendees to 2” if more than 2 are selected.

Hope I am making things clear–thanks!

[Suvrutt_Gurjar]

In general, I believe , yes, you should be able to write an IFS(), IF() based valid if for multiple conditions and associated error message(s) as well.

Edit: So your Valid_if error message could be something like

IFS (
AND( “condition of attendee NON available”=FALSE, “condition for attendee number EXCEEDING 2”=TRUE), “Please limit attendees to 2” ,

AND( “condition of attendee NON available”=TRUE, “condition for attendee number EXCEEDING 2”=FALSE), “Attendee is unavailable for time selected” ,

AND( “condition of attendee NON available”=TRUE, “condition for attendee number EXCEEDING 2”=TRUE), “Attendee is unavailable for time selected AND Please limit attendees to 2 ”

)

Edit 2: Added missing parenthesis for the AND() conditions.

[Jason_Stetler]

For some reason my IF() statement in the invalid value error is not responding properly.

My conditions still allow for an error to be noticed, but the error message simply says “This entry is invalid”

[Suvrutt_Gurjar]

It will be possible to suggest an exact expression , if feasible , if you are able to share the Valid_if expression that is working.

In the edited post above, I have shared a suggested construct for the error message based on the information you have given thus far.

[Steve]

Error message text is produced by an expression in Invalid value error: