Formula with many conditions required please

[Orangeanorak]

Grateful if a formula can be supplied for this one please. No VBA if possible
Column E deadline
If D5 = Emergency and the date in A5 is equals today insert text "In Progress" if = 1 day more insert text "Expires Today" if more than 1 day insert text "Expired"
If D5 = Urgent and the date in A5 is less than 3 days ahead insert text "In Progress" if = 3 days ahead insert text "About to Expire" if 4 days ahead insert text "Expires Today" if more than 4 days insert text "Expired"
If D5 = Routine and the date in A5 is less than 24 days ahead insert text "In Progress" if 24 to 27 days ahead insert text "About to Expire" if 28 days ahead insert text "Expires Today" if more than 28 days insert text "Expired"

Thank you --------- Mission Impossible ?????

 

[Orangeanorak]

You're welcome, glad we finally figured it out, and it works for you.
And by the way, it's jtakw.

My apologies jtakw - my keyboard skills are almost as bad as my excel skills!!
May I ask you one more thing please.
If there is no date in Col A can Col D be blank
Thank you

 

[jtakw]

No problem, just add a check for blanks in A:

Book3.xlsx
	A	B	C	D	E
4	Date reported			Status	Deadline
5	1/19/2022			Emergency	Expires Tomorrow
6	1/18/2022			Emergency	Expires Today
7	1/17/2022			Emergency	Expired
8	1/19/2022			Urgent	In Progress
9	1/18/2022			Urgent	In Progress
10	1/17/2022			Urgent	In Progress
11	1/16/2022			Urgent	Expires Tomorrow
12	1/15/2022			Urgent	Expires Today
13	1/14/2022			Urgent	Expired
14	1/19/2022			Routine	In Progress
15	12/29/2021			Routine	In Progress
16	12/28/2021			Routine	About to Expire
17	12/24/2021			Routine	About to Expire
18	12/23/2021			Routine	Expires Tomorrow
19	12/22/2021			Routine	Expires Today
20	12/21/2021			Routine	Expired
21
22
Sheet943

Cell Formulas
Range	Formula
E5:E22	E5	=IF(A5="","",IFERROR(LOOKUP(A5-TODAY()+IF(D5="Emergency",1,IF(D5="Urgent",4,28)),IF(D5="Routine",{0,1,2,7},{0,1,2}),IF(D5="Routine",{"Expires Today","Expires Tomorrow","About to Expire","In Progress"},{"Expires Today","Expires Tomorrow","In Progress"})),"Expired"))

 

[Orangeanorak]

Thank you - just some drop downs and conditional formatting and thats me sorted - exceptionally helpful.

 

[jtakw]

You're very welcome, thanks for the feedback.

 

[Orangeanorak]

You're very welcome, thanks for the feedback.

Hi - sorry to bother you again jtakw - would it be possible to add another criteria to the formula please

If D5 is Long Term - if A5 is equal to or up to 80 days from today show "In Progress" if 81 to 88 days from today show "About to Expire", if 89 days show "Expires Tomorrow, if 90 days show "Expires Today, if more than 90 days show "Expired"

Thank you!

 

[jtakw]

jtakw - would it be possible to add another criteria to the formula please

If D5 is Long Term - if A5 is equal to or up to 80 days from today show "In Progress" if 81 to 88 days from today show "About to Expire", if 89 days show "Expires Tomorrow, if 90 days show "Expires Today, if more than 90 days show "Expired"

Thank you!

This should do it, test it on your data to confirm:

Book3.xlsx
	A	B	C	D	E
4	Date reported			Status	Deadline
5	1/22/2022			Emergency	Expires Tomorrow
6	1/21/2022			Emergency	Expires Today
7	1/20/2022			Emergency	Expired
8	1/22/2022			Urgent	In Progress
9	1/21/2022			Urgent	In Progress
10	1/20/2022			Urgent	In Progress
11	1/19/2022			Urgent	Expires Tomorrow
12	1/18/2022			Urgent	Expires Today
13	1/17/2022			Urgent	Expired
14	1/22/2022			Routine	In Progress
15	1/1/2022			Routine	In Progress
16	12/31/2021			Routine	About to Expire
17	12/27/2021			Routine	About to Expire
18	12/26/2021			Routine	Expires Tomorrow
19	12/25/2021			Routine	Expires Today
20	12/24/2021			Routine	Expired
21	1/22/2022			Long Term	In Progress
22	11/3/2021			Long Term	In Progress
23	11/2/2021			Long Term	About to Expire
24	10/26/2021			Long Term	About to Expire
25	10/25/2021			Long Term	Expires Tomorrow
26	10/24/2021			Long Term	Expires Today
27	10/23/2021			Long Term	Expired
Sheet943

Cell Formulas
Range	Formula
E5:E27	E5	=IF(A5="","",IFERROR(LOOKUP(A5-TODAY()+IF(D5="Emergency",1,IF(D5="Urgent",4,IF(D5="Routine",28,90))),IF(D5="Routine",{0,1,2,7},IF(D5="Long Term",{0,1,2,10},{0,1,2})),IF(OR(D5={"Routine","Long Term"}),{"Expires Today","Expires Tomorrow","About to Expire","In Progress"},{"Expires Today","Expires Tomorrow","In Progress"})),"Expired"))

 

[Orangeanorak]

This should do it, test it on your data to confirm:

Book3.xlsx
	A	B	C	D	E
4	Date reported			Status	Deadline
5	1/22/2022			Emergency	Expires Tomorrow
6	1/21/2022			Emergency	Expires Today
7	1/20/2022			Emergency	Expired
8	1/22/2022			Urgent	In Progress
9	1/21/2022			Urgent	In Progress
10	1/20/2022			Urgent	In Progress
11	1/19/2022			Urgent	Expires Tomorrow
12	1/18/2022			Urgent	Expires Today
13	1/17/2022			Urgent	Expired
14	1/22/2022			Routine	In Progress
15	1/1/2022			Routine	In Progress
16	12/31/2021			Routine	About to Expire
17	12/27/2021			Routine	About to Expire
18	12/26/2021			Routine	Expires Tomorrow
19	12/25/2021			Routine	Expires Today
20	12/24/2021			Routine	Expired
21	1/22/2022			Long Term	In Progress
22	11/3/2021			Long Term	In Progress
23	11/2/2021			Long Term	About to Expire
24	10/26/2021			Long Term	About to Expire
25	10/25/2021			Long Term	Expires Tomorrow
26	10/24/2021			Long Term	Expires Today
27	10/23/2021			Long Term	Expired
Sheet943

Cell Formulas
Range	Formula
E5:E27	E5	=IF(A5="","",IFERROR(LOOKUP(A5-TODAY()+IF(D5="Emergency",1,IF(D5="Urgent",4,IF(D5="Routine",28,90))),IF(D5="Routine",{0,1,2,7},IF(D5="Long Term",{0,1,2,10},{0,1,2})),IF(OR(D5={"Routine","Long Term"}),{"Expires Today","Expires Tomorrow","About to Expire","In Progress"},{"Expires Today","Expires Tomorrow","In Progress"})),"Expired"))

All tested - perfect - Thank you - I will try not to bother you again. Enjoy the rest of your weekend

 

[jtakw]

All tested - perfect - Thank you - I will try not to bother you again. Enjoy the rest of your weekend

You're welcome, it's no bother at all, if you need further adjustments, just post back.
You also have a good weekend.

 

[Orangeanorak]

Hi jtakw - I have now completed my spreadsheet named Schedule - Thank you for your help.
I now wish to copy cells from the schedule to a number of forms/documents (1-10). I have attached a copy of the cells I wish to Copy from Schedule to Form 1 - I can do this by direct reference between form 1 and schedule taking data from the first row ---- row 5
Schedule to Form 1
A5 to C3
C5 to C8
C5 to C9
I5 to C11
K5 to C12
J5 to E12
L5 to A13:F22
D5 :E26
A5 to C26


Is it possible to set up a formula to copy the relevant cells in Schedule row 6 to form 2, row 7 to form 3, row 8 to form 4 and so on - I will probably need this for 100 forms but dont wish to do it manually.

Thank you - I hope I have explained this properly

 

[jtakw]

As the formulas would Have to be in the Target Sheet (e.g. form 1, form 2, form3, etc.) and Not the Schedule Sheet, you will, unfortunately have to Manually enter those formula in each form/sheet.
You can probably get this done by VBA, but I'm not strong with VBA and unable to help using this method.