Anonymous
Share this content
0
220

Excel conditional formatting

Want to highlight cells in Col A where cells in any of Col Z, Col AG, and Col AZ are highlighted

I have a large spreadsheet - perhaps 50 cols.  The cols are laid out in a way that matches the workflow - in order to help the staff populating the cells.  Col Z is populated with the date that a key task has been completed.  Cols AG and BA are similalry populated for two other key tasks.  Obviously its easy to use conditional formatting to highlight and cells where a completion date has not been entered

Col A is populated with the identity of that particular project.

What I want to do is have the relevant cell in Col A automatically highlighted in the cells in any of Cols Z, AG or  BA have been highlighted.  This would allow me to scan down just one Col to spot any projects which have been highlighted as failing at least one of the 3 date checks.

Probably very easy for many on here - but not me (!) so thanks in advance

Roger

 

 

Replies

Please login or register to join the discussion.

12th Oct 2017 15:29

Hi Roger,
It might help clarify and create consistency if you were to post the conditional formulae from columns Z, AG & BA.

Thanks (0)
avatar
By Grundy
12th Oct 2017 16:25

Adjust the below for correct columns and take away the spaces

=OR( S4 < NOW(), T4 < NOW(), U4 < NOW() )

Thanks (0)
to Grundy
13th Oct 2017 10:19

Quote:

Adjust the below for correct columns and take away the spaces

=OR( S4 < NOW(), T4 < NOW(), U4 < NOW() )

Maybe > rather than < ?

Maybe a spreadsheet for job management is not the right tool? Does your tax/PM/billing software not have workflow tracking (e.g. Iris jobs).

Thanks (0)
avatar
By Grundy
to alan.rolfe
13th Oct 2017 16:56

In this situation they want to know if a deadline has been exceeded, therefore:

If today’s date is greater than deadline, highlight

This could be adjusted for earlier warning with a now()-1 or -2 etc

Thanks (0)
13th Oct 2017 09:13

Your condition can't be whether the cells are highlighted, rather, as Grundy has said, you need to use an OR statement to group together the three conditions for highlighting from the three columns you are considering.

Thanks (0)
avatar
13th Oct 2017 13:02

Just as an aside, you don't need to remove the spaces.

Thanks (0)
Share this content