Using IF formula to reference any change in cell (adding a value)

Nicole Haste

Hello Experts!

I was wondering if a formula like this is possible:

=IF([Confirmation #]@row = "any value", "Confirmed")

When a confirmation number is added to a cell, that means the item is confirmed. What can I put in place of "any value'? I am just not sure how to do this in the context of my larger status formula:

=IFERROR(IF([Ordered Date]@row = "", "New", IF(TODAY() > [Arrival Date]@row, "Received", IF([Confirmation #]@row > 1, "Confirmed", IF([Ordered Date]@row <= TODAY(), "Ordered")))), "")

This formula works with existing data but doesn't work with new data (when add todays date to Ordered Date column, switches to Received (no other date columns are filled out))

Any help would be amazing!

Nicole

Danielle O'Connell

For the first part of your question I would use the ISBLANK and NOT function:

=IF(NOT(ISBLANK([Confirmation #]@row)), "Confirmed", *****you need a false value here******)

Although your formula above with the greater than 1 should work.

If I understand what you're describing in the second part of this post, it might just be the order of your formula. Today will always be greater than nothing. Since that is your second IF statement, you will always get a "Received" even if Arrival date is blank and you will never get the rest of your IF statements. I would try changing the order. You could also use for the Today part:

IF(AND(NOT(ISBLANK([[Arrival Date]@row)), TODAY()>[Arrival Date]@row, "Received", IF....

This one will then give you the false value if the arrival date is blank and the true value if today is greater than the arrival date. You could even simplify this to just using NOT(ISBLANK() for arrival date if you never have any instance where someone might put in an anticipated date of arrival that is in the future.

Danielle O'Connell

For the first part of your question I would use the ISBLANK and NOT function:

=IF(NOT(ISBLANK([Confirmation #]@row)), "Confirmed", *****you need a false value here******)

Although your formula above with the greater than 1 should work.

If I understand what you're describing in the second part of this post, it might just be the order of your formula. Today will always be greater than nothing. Since that is your second IF statement, you will always get a "Received" even if Arrival date is blank and you will never get the rest of your IF statements. I would try changing the order. You could also use for the Today part:

IF(AND(NOT(ISBLANK([[Arrival Date]@row)), TODAY()>[Arrival Date]@row, "Received", IF....

This one will then give you the false value if the arrival date is blank and the true value if today is greater than the arrival date. You could even simplify this to just using NOT(ISBLANK() for arrival date if you never have any instance where someone might put in an anticipated date of arrival that is in the future.