Sep 23 2022 12:10 PM
Sep 23 2022 12:10 PM
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.
Sep 24 2022 03:31 PM
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
"Shared" if the value in B2 has any duplicates anywhere else in column B
"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.
Sep 25 2022 05:51 AM
This spreadsheet tracks all types of space within an office complex. Seats for employees, as well as other spaces.
"Alone" represents a space occupied by an employee where he resides in the space by himself.
"No" Is an arbitrary term indicating that the subject Excel row represents a space other than a seat for an employee. Examples would be a janitor closet, hallway, lobby, etc.
Sep 25 2022 08:42 AMSolution
Up to now, your examples didn't have "One Desk Setup", so the formula didn't take that into account.
=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")
Sep 26 2022 01:01 PM
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",C2="One Desk Setup","Alone",COUNTIFS($B:$B,$B2,$A:$A,"<>*None",$A:$A,"<>*Common",$A:$A,"<>*Hotel")>1,"Shared",TRUE,"Alone")
PS, a request: if you have more variants in the future: please provide a sample workbook with all possible combinations instead of just the "new" one.
Sep 26 2022 01:52 PM