IF Function- To reflect changes when another column changes

[Rile1]

Hi

Need your guidance

I have this following table and I need to add a formula on Column E (i.e. Next Status) which, if Column B changes (i.e. Status) then the Column E (i.e. Next Status) should also reflect a change


Excel table

A	B	C	D	E	H	Z
Date	Status	Travel Agent Name	Airline Name	Next Status	Log_Date	Booking Created Date
21-Aug-18	Pending Booking	ABC	789	Escalation to Travel Manager	1-Aug-18
21-Aug-18	Pending Presentation	123	GHI	FALSE	15-Aug-18	17-Aug-18
21-Aug-18	Pending Booking	DEF	101	1st Notification to Travel Agent	16-Aug-18	18-Aug-18
21-Aug-18	Pending Presentation	456	JKL	FALSE	16-Aug-18	17-Aug-18

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The formula which i am trying is the following
=IF(B19="Pending Booking",IF(A19<=H19+7,"1st Notification to Travel Agent",IF(A19>H19+7<=H19+14,"2nd Notification to Travel Agent",IF(A19>=H19+14,"Escalation to Travel Manager", OR(IF(B19="Pending Presentation",IF(A19<=Z19+2,"1st notification to Airline",IF(A19>Z19+2<=Z19+4,"2nd Notification to Airline",IF(A19>Z19+4,"Escalation to Airline Ground Manager")))))))))

Please if you could assist on the formula as Row 2 and 4, doesn't reflect properly

Thanking you for the assistance

 

[AlanY]

try this

=IF(B19="Pending Booking",IF(A19<=H19+7,"1st Notification to Travel Agent",IF(A19>H19+7<=H19+14,"2nd Notification to Travel Agent","Escalation to Travel Manager")),IF(A19<=Z19+2,"1st notification to Airline",IF(A19>Z19+2<=Z19+4,"2nd Notification to Airline","Escalation to Airline Ground Manager")))

 

[AlanY]

this is more readable

=IF(B19="Pending Booking",IF(A19>H19+14,"Escalation to Travel Manager",IF(A19>H19+7,"2nd Notification to Travel Agent","1st Notification to Travel Agent")),IF(A19>Z19+4,"Escalation to Airline Ground Manager",IF(A19>Z19+2,"2nd Notification to Airline","1st notification to Airline")))

 

[wideboydixon]

This bit doesn't make sense:

A19>H19+7<=H19+14

This is where I got to:

=IF($B19="Pending Booking",IF($A19<=$H19+7,"1st Notification to Travel Agent",IF($A19<=$H19+14,"2nd Notification to Travel Agent","Escalation to Travel Manager")),IF($B19="Pending Presentation",IF($A19<$Z19+2,"1st Notification to Airline",IF($A19<$Z19+4,"2nd Notification to Airline","Escalation to Airline Ground Manager")),""))

WBD

 

[Rile1]

Thank you AlanY and WBD for the great help

 

[AlanY]

you're welcome