Change text of one cell based on the color of another cell.

[Eric Livesay]

Hello!

Once again I turn to the mighty comprehensive knowledge of The Board.

I have a spreadsheet on excel 2007 that tracks info on several hundred people. The info is from each persons personell folder.
I have columns D thru M that tracks personal info and then in column O i have a "Folder Complete" column that i use to indicate if that persons folder is good to go.

My problem is that when i change anything in columns D thru M to a negative response i have to manually change column O from Yes to No. Another issue is that i have several date formulas in columns D thru M that automatically change the color of the cell to red based on certain requirements.

The basic question is how can i format cells O5:O350 to change the text from a "YES" to a "NO" based on the red color of a cell in columns D5:M350? I already have conditional formatting set to turn the cell red if the cell is "NO".

My guess is VBA, but I hope not as the security on my work computer doesn't allow VBA code to run. I hope it's just a formula.

Thanks in advance!

Eric.

 

[Jerry Sullivan]

Hello Eric,

I'm not aware of any way to read the color of a cell using formulas alone.

Since each of your conditional formatting rules defines a test formula which, when true, formats the cell, it should be possible for you to make a formula in Column that tests if all those separate formulas are true.

A typical formula in Column O might have this pattern:
=if(and(D3="Done", E3>=$E$1, ISBLANK(F3)),"NO","YES")

Depending on the complexity of your ~10 formulas for Columns D:M this could get lengthy ...but it is one way to solve this with formulas alone.