Auto update of Cell Values based on Date Input from Previous Column

[kylekhmis]

Hi All,

I need your help! Im still new to VBA. How do I auto-update the replacement status column based on the date input from previous columns. Here's what I mean:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Column A [/TD]
[TD="align: center"]Column B [/TD]
[TD="align: center"]Column C[/TD]
[TD="align: center"]Column D [/TD]
[TD="align: center"]Column E[/TD]
[/TR]
[TR]
[TD]Date of Card Application [/TD]
[TD]Date Card Collected from Post Office[/TD]
[TD]Date Card Issued to Customer [/TD]
[TD]Temp Card Returned ? (Yes/No) [/TD]
[TD]Replacement Status [/TD]
[/TR]
[TR]
[TD]24/02/2016[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Card Applied. Please collect card when ready.[/TD]
[/TR]
</tbody>[/TABLE]

So for e.g.

If Date of Card Application is 24/02/2016, the replacement status will be shown as "Card Applied. Please Collect Card When Ready".

If Date of Date Card Collected from Post Office is 2/03/2016, the replacement status will be shown as "Card Collected. Please Issue to Customer".

If Date Card Issued to Customer is 2/04/2016, the replacement status will be shown as "Card Issued. Please remind customer to return TEMP card".

If Temp Card Returned ? (Yes/No) is YES, the replacement status will be shown as " Temp Card Returned. Replacement Completed".If Temp Card Returned ? (Yes/No) is NO, the replacement status will be shown as " Temp Card not returned, please remind customer".


Anyone can assist me with the codes?? & I need the code running for the entire worksheet,when I input a new data in other rows.

 

[tygrrboi]

Does it have to be those specific dates? or are you just looking for any date in those areas?

 

[sericom]

If all columns get filled out, and from left to right, then you could use this formula

=IFERROR(CHOOSE(IF(D2="No",5,COUNTA(A2:D2)),"Card Applied. Please Collect Card When Ready","Card Collected. Please Issue to Customer","Card Issued. Please remind customer to return TEMP card","Temp Card Returned. Replacement Completed","Temp Card not returned, please remind customer"),"")

 

[kylekhmis]

Thank you so much Sericom! This works perfectly! ?????????

 

[kylekhmis]

Does it have to be those specific dates? or are you just looking for any date in those areas?

If all columns get filled out, and from left to right, then you could use this formula

=IFERROR(CHOOSE(IF(D2="No",5,COUNTA(A2:D2)),"Card Applied. Please Collect Card When Ready","Card Collected. Please Issue to Customer","Card Issued. Please remind customer to return TEMP card","Temp Card Returned. Replacement Completed","Temp Card not returned, please remind customer"),"")

Thank you so much! This works perfectly! ?????????