Condition formatting of multiple condition formatted cells

[merrin84]

where to start .....


well first a hello as I'm new here, but have started to use excel more and more. I have started to realise that it is a very powerful and useful tool if only I knew how to use it

Ok, I have condition monitoring down to monitor dates. The date cell is green for OK, amber for date within the next month of today and red if date has expired... that's repeated through around 30 columns .

My question is can I monitor all those columns as a summary in 1 cell ??

Example:

C1 through to X1 all have condition formatting in their own right, is it possible to have A1 show green if all A1 to X1 are green (or logic tests are true). If any one of the cells from C1 to X1 to go amber or red then A1 would need to be changed to the corresponding colour.

I'm guessing its a logic test but unsure how to monitor such a big selection of cells, maybe colour or the original condition monitoring of the cells.

Thanks in advance

[merrin84]

sorry for the title wasn't sure how to explain it :/

[AliGW]

This is probably best achieved using a helper column (which could be hidden) to which the one cell you want would refer. The hidden column would check the range and return a text string such as R, A or G (for red, amber or green), then the conditional formatting in the one cell would use a formula referring to this helper column to determine its colour by adding three rules (one for each colour).

[merrin84]

so 1 helper column to the side of the summary ?

that makes sense so I could use simple conditional monitoring of that cell, do you know any sample formulas to monitor the range of cells ?

[AliGW]

It depends on the formulae already used, really. Every situation will be different. Attach the file if you want specific advice.

[merrin84]

this is the test document

Can you recommend any books to learn more about conditional formatting ?

[sourabhg98]

Hi
You can use a min function
First format all summary cells as green
Now use a min function like this
So first you need to check whether a value is smaller then today...and if yes then it should be red...so we can just simply test the minimum value from the range like this
=MIN(C2:J2)<TODAY() ...if any value will be smaller than today...then it will give true...
So just using min function ...develop a formula like this

=IF(MIN(C2:J2)<TODAY(),3,IF(MIN(C2:J2)<(TODAY()+30),2,1))=2

Before using this first format all the summary cells as green
Now select all summary cells and use the above formula and select format as amber
Now for red use this
=IF(MIN(C2:J2)<TODAY(),3,IF(MIN(C2:J2)<(TODAY()+30),2,1))=3

Hope this helps

[merrin84]

That's cracked that thank you

couple of other bits, I have lots to learn.

If I didn't want to default the green cell could that be done ?

I'm hoping to add a text description to the formatted colour

[merrin84]

dam i wish i could crack this lol

[sourabhg98]

Yes
Then use this one and format as green
=IF(MIN(C2:J2)<TODAY(),3,IF(MIN(C2:J2)<(TODAY()+30),2,1))=1

Actually this formula
=IF(MIN(C2:J2)<TODAY(),3,IF(MIN(C2:J2)<(TODAY()+30),2,1))

returns 1 for green, 2 for amber and 3 for red...so you can use any way you want...

[merrin84]

ok well my idea didnt work, i have all three formulas working for the RAG.

The next stage was to input a text string to sit in the same cell. I thought i'd be clever and monitor the outcome of each rule to deliver the text string but doesnt work. What am i doing wrong ??

or is it easier to include a text string in the orginal formula ?

[sourabhg98]

Do you need a text to display? like for green summary cell you need "text1" for amber - "text2" and for red "text3"
Then you can use this formula in summary cells..
=IF(MIN(C2:J2)<TODAY(),"text3",IF(MIN(C2:J2)<(TODAY()+30),"text2","text1"))
Sorry if this is not what are you trying to get...

[merrin84]

the description seems right but I've added that and don't seem to work.

A few other bits, sorry if I'm being a pain.

all the formatting watches row 2, how do I get it to automatically watch the row the summary sits on ?

since adding the new rule, the amber rule now doesn't watch 31 days ?

Can I add a 4th condition, for it to watch missing data from the row and produce a colour and text "missing data". I'm assuming its around ""