Forum Discussion
Error Checking Formula
On the attached workbook, I have 4 rows. These 4 rows detail the state of 2 offices, with 2 desks each.
Office 300 has two desks. One is Occupied and one is Vacant.
Office 301 has two desks. Both are Occupied.
In all offices, I want to indicate a Seat as “Alone” if the second desk in the room is Vacant.
In all offices, I want to indicate a Seat as “Shared” if the second desk in the room is also Occupied.
I’m currently double checking my work manually, because I can’t find a way to create an Error Checking Formula (ECF) to do this for me. If I had an ECF that checked this for me, I would check the ECF before I saved the workbook, go back to see what I did wrong, and correct it.
Can someone help me create an ECF like the example shown, to accomplish this task?
Alternatively, if there’s a way for the “Seat” cell to automatically populate when criteria is met, that would be even cleaner.
Up to now, your examples didn't have "One Desk Setup", so the formula didn't take that into account.
New one:
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",OR(C2="One Desk Setup",COUNTIFS($B:$B,$B2,$A:$A,"*None")>0),"Alone",TRUE,"Shared")
In G2:
=IF(A2="*None","V",IF(COUNTIFS($B$2:$B$100,$B2,$A$2:$A$100,"*None")=0,"Shared","Alone"))
Adjust the ranges if the data extend below row 100.
Fill down.
Hans, I really appreciate your response. Thank you!
Here's where you'll begin to dislike me....
What if I wanted the text in cell G2 to be:
"V" if the text in cell A2 is *None
"Comm", if the text in cell A2 is Common
"Hotel", if the text in cell A2 is Hotel
"No", if the text in cell A2 is anything other than Common, Hotel, or *None
and
"Shared" if the value in B2 has any duplicates anywhere else in column B
or
"Alone" if the value in B2 does not have any duplicates anywhere else in column B
I apologize for not asking this originally. I'm assuming a different formula is needed, based on the concept of a logical test being either one thing or another; Not one of 4 things or another.
I'd appreciate any help you can provide.
I don't understand the distinction between "No" and "Alone".
