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!!!

 

[Jeffrey Mahoney]

Here's one solution
=IFS(ISCELLDATE(CL3),"RETURNED",ISCELLDATE(CK3),"HOLD",ISCELLDATE(CE3),"FAOC",ISCELLDATE(BK3),"COMPLETE",ISCELLDATE(S3),"ACTIVE")

The order in which you place those in the formula determines which result you get. You'll have to add this UDF to a standard module in VBA

Function ISCELLDATE(Cel As Range) As Boolean
  ISCELLDATE = ISDATE(Cel)
End Function

 

[shift-del]

Try this.

Mappe33
	CM
5	RETURNED
Tabelle1

Cell Formulas
Range	Formula
CM5	CM5	=IFS(CL5<>"","RETURNED",CK5<>"","HOLD",CE5<>"","FAOC",BK5<>"","COMPLETE",S5<>"","ACTIVE")

 

[Kellie220]

Here's one solution
=IFS(ISCELLDATE(CL3),"RETURNED",ISCELLDATE(CK3),"HOLD",ISCELLDATE(CE3),"FAOC",ISCELLDATE(BK3),"COMPLETE",ISCELLDATE(S3),"ACTIVE")

The order in which you place those in the formula determines which result you get. You'll have to add this UDF to a standard module in VBA

Function ISCELLDATE(Cel As Range) As Boolean
  ISCELLDATE = ISDATE(Cel)
End Function

Hi there thank you for the response. I tried it, I am not sure what I am doing wrong, but it either gives me #NA or "Returned". When we start the process, the status is "ACTIVE" right away. My Data starts on Row 11. I do have other VBA script in this workbook. I am not sure if that is interfering with the process.

 

[Kellie220]

Try this.

Mappe33
	CM
5	RETURNED
Tabelle1

Cell Formulas
Range	Formula
CM5	CM5	=IFS(CL5<>"","RETURNED",CK5<>"","HOLD",CE5<>"","FAOC",BK5<>"","COMPLETE",S5<>"","ACTIVE")

Hi thank you for the response. This is not working for me. I am not sure what the issue is. My work starts on Row 11, and when I input what you suggested it gives me "returned" on all. That is my column CL and typically out of about 6000 we have maybe 100. The column never gets a date normally.

 

[Kellie220]

Hi there thank you for the response. I tried it, I am not sure what I am doing wrong, but it either gives me #NA or "Returned". When we start the process, the status is "ACTIVE" right away. My Data starts on Row 11. I do have other VBA script in this workbook. I am not sure if that is interfering with the process.

Maybe it could be that of those 5 cells mentioned above, not all will have a date. Some never get a date. Column S, BK, and CE will get dates but eventually, over time. Column S will always have a date, then progress to the other 2 columns. So those columns will be blank in the beginning.

 

[shift-del]

Please show some sample data and expected results with XL2BB.

Mappe33
	S	T																																											BK	BL																			CE	CF					CK	CL	CM
5	04.04.2024																																																																								ACTIVE
6	04.04.2024																																																																10.05.2024								FAOC
7	04.04.2024																																																																10.05.2024						12.05.2024		HOLD
Tabelle1

Cell Formulas
Range	Formula
CM5:CM7	CM5	=IFS(CL5<>"","RETURNED",CK5<>"","HOLD",CE5<>"","FAOC",BK5<>"","COMPLETE",S5<>"","ACTIVE")

 

[Kellie220]

 

[shift-del]

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")

 

[Kellie220]

Please show some sample data and expected results with XL2BB.

Mappe33
	S	T																																											BK	BL																			CE	CF					CK	CL	CM
5	04.04.2024																																																																								ACTIVE
6	04.04.2024																																																																10.05.2024								FAOC
7	04.04.2024																																																																10.05.2024						12.05.2024		HOLD
Tabelle1

Cell Formulas
Range	Formula
CM5:CM7	CM5	=IFS(CL5<>"","RETURNED",CK5<>"","HOLD",CE5<>"","FAOC",BK5<>"","COMPLETE",S5<>"","ACTIVE")

Hi Good Morning Please see my screenshot below. I entered a few dates but nothing changed.