Autopopulate Cells when meeting 3 conditions

[marcelkahn5]

Hi Excel Forum Fam.,

I'm reaching out for some assistance to see how I can achieve the following:


Enter Today's Date in the "New Column" for all N/A's and 0's that fall in the "Action Column" and Highlighted Red.

Cells are Red if they are either <= to Today's date.



I've attached a spreadsheet for easier understanding.

I hope this is possible!

Thank you very much!
MK

[FDibbins]

...Cells are Red if they are either <= to Today's date...

Based on what? I dont see any dates in your table.

For the other part, try this...
=IF(OR(I2=0,I2="N/A"),TODAY(),"")
Format as DATE

[marcelkahn5]

The Action Column pulls in data from another tab.

How do I autopopulate Column M (New) with Todays date if Column I (Action) shows #N/A or 0, and Column E (Date) shows a date <= to today's date.

[FDibbins]

A few observations...
1. You need to be consistent with how you enter N/A. I see you now have a #N/A, can that be changed to N/A?
2. You did not say what you wanted if I is blank?

Assuming you want nothing shown if I is empty...
M2=IF(AND(I2<>"",E2<=TODAY(),OR(I2=0,I2="N/A")),TODAY(),"")
copied down
Format as Date

[marcelkahn5]

I'll make sure to keep #N/A consistent.

Column I will never be blank.

Is it possible to combine this formula with my Vlookup formula that's pulling in data for Column I? and automatically replace the #N/A's and 0's while keeping all the existing data that's pulled in for all entries that are not #N/A or 0?

Really appreciate you taking the time!

Thank you!

[FDibbins]

...Instead of blank, I’ll need the current entries in Column I to remain.

OK, but what do you want M to show is I is empty?

So this formula would have to be combined with my blood up that’s pulling data into Column I if that’s possible.

Not really sure what that means?