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
[TABLE="width: 765"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]H[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Travel Agent Name[/TD]
[TD]Airline Name[/TD]
[TD]Next Status[/TD]
[TD]Log_Date[/TD]
[TD]Booking Created Date[/TD]
[/TR]
[TR]
[TD]21-Aug-18[/TD]
[TD]Pending Booking[/TD]
[TD]ABC[/TD]
[TD]789[/TD]
[TD]Escalation to Travel Manager[/TD]
[TD]1-Aug-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21-Aug-18[/TD]
[TD]Pending Presentation[/TD]
[TD]123[/TD]
[TD]GHI[/TD]
[TD]FALSE[/TD]
[TD]15-Aug-18[/TD]
[TD]17-Aug-18[/TD]
[/TR]
[TR]
[TD]21-Aug-18[/TD]
[TD]Pending Booking[/TD]
[TD]DEF[/TD]
[TD]101[/TD]
[TD]1st Notification to Travel Agent[/TD]
[TD]16-Aug-18[/TD]
[TD]18-Aug-18[/TD]
[/TR]
[TR]
[TD]21-Aug-18[/TD]
[TD]Pending Presentation[/TD]
[TD]456[/TD]
[TD]JKL[/TD]
[TD]FALSE[/TD]
[TD]16-Aug-18[/TD]
[TD]17-Aug-18[/TD]
[/TR]
</tbody>[/TABLE]

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