Status column with multiple variables

[Kellie220]

HI All!!! I have an excel question. My spreadsheet has about 85 columns and about 6000 rows.

My Column "P" is my status column. It is based on 5 different columns. So far I just manually enter in the status but was hoping I could get a formula for the status.

Column "S" if this has a date then my status column "P" would state "ACTIVE"
Column "BK" if this has a date then my status column "P" would state "COMPLETE"
Column "CE" if this has a date then my status column "P" would state "FAOC"
Column "CK" if this has a date then my status column "P" would state "HOLD"
Column "CL" if this has a date then my status column "P" would state "RETURNED"

The tricky part is that there could be dates in each of the columns, S, BK, CE, and CK or CL. These date are the evolution of the assignment with the exclusion of CK & CL as that means the assignment has issues.

The assignment goes from Active, to Complete to FAOC (when acctg purges it off our system). Really 3 dates if no issues. Is there a formula that I can enter with these variables so I do not always have to update the Status column P? Thank you so much!!!

 

[Kellie220]

There is SOMETHING in columns BK, CE, CK, Cl.
See the difference in my spreadsheet. The cells in rows 10:13 are really empty. The cells in rows 16.20 contain a space.

Mappe33
	S	T																																											BK	BL																			CE	CF					CK	CL	CM
10	08.11.2023																																																																								ACTIVE
11	15.03.2024																																																																02.04.2024								FAOC
12	15.03.2024																																																																						02.04.2024		HOLD
13	15.03.2024																																																																							02.04.2024	RETURNED
14
15
16	08.11.2023																																																																								RETURNED
17	15.03.2024																																																																02.04.2024								RETURNED
18	15.03.2024																																																																						02.04.2024		RETURNED
19	15.03.2024																																																																							02.04.2024	RETURNED
Tabelle1

Cell Formulas
Range	Formula
CM10:CM13,CM16:CM19	CM10	=IFS(CL10<>"","RETURNED",CK10<>"","HOLD",CE10<>"","FAOC",BK10<>"","COMPLETE",S10<>"","ACTIVE")

Ok, let me try clearing my spreadsheet.

 

[Kellie220]

There is SOMETHING in columns BK, CE, CK, Cl.
See the difference in my spreadsheet. The cells in rows 10:13 are really empty. The cells in rows 16.20 contain a space.

Mappe33
	S	T																																											BK	BL																			CE	CF					CK	CL	CM
10	08.11.2023																																																																								ACTIVE
11	15.03.2024																																																																02.04.2024								FAOC
12	15.03.2024																																																																						02.04.2024		HOLD
13	15.03.2024																																																																							02.04.2024	RETURNED
14
15
16	08.11.2023																																																																								RETURNED
17	15.03.2024																																																																02.04.2024								RETURNED
18	15.03.2024																																																																						02.04.2024		RETURNED
19	15.03.2024																																																																							02.04.2024	RETURNED
Tabelle1

Cell Formulas
Range	Formula
CM10:CM13,CM16:CM19	CM10	=IFS(CL10<>"","RETURNED",CK10<>"","HOLD",CE10<>"","FAOC",BK10<>"","COMPLETE",S10<>"","ACTIVE")

Ok, that worked, I had INDEX/MATCH to other reports to get some of my information, I cleared out that info and re-added. It did work. I have one side question. Is there a way to make the status blank if nothing is entered? When we assign the project is when it goes active. Sometimes we enter info and are waiting to assign, so no date would be entered.

 

[shift-del]

Mappe33
	S	T																																											BK	BL																			CE	CF					CK	CL	CM
8
Tabelle1

Cell Formulas
Range	Formula
CM8	CM8	=IFS(CL8<>"","RETURNED",CK8<>"","HOLD",CE8<>"","FAOC",BK8<>"","COMPLETE",S8<>"","ACTIVE",TRUE,"")

 

[Kellie220]

Mappe33
	S	T																																											BK	BL																			CE	CF					CK	CL	CM
8
Tabelle1

Cell Formulas
Range	Formula
CM8	CM8	=IFS(CL8<>"","RETURNED",CK8<>"","HOLD",CE8<>"","FAOC",BK8<>"","COMPLETE",S8<>"","ACTIVE",TRUE,"")

Thank you