Conditional Formatting - 3 Rules - Excel 2000

[AT]

Hi, I am using Excel 2000 and need to use Conditional Formatting to visually
represent areas of concern. I have one worksheet holding the core data,
linked to a 2nd sheet which provides a summary where the formatting is used.
I am using 3 colours to highlight data according to their value, Green,
Yellow, Red, with the following settings:

Green: 90 or above
Yellow: Between 60 and 90
Red: Between 1 and 60

So if 91 is in cell a1, the cell automatically changes to Green and so
forth. The conditional formatting works fine when data is entered directly
into the cell. However, when it is linked and the linked cell has no data in
it, it automatically turns Green when the formatting is applied. When i put
zero in on the data sheet, it comes up with a #DIV/0! error msg. I have used
the following to suppress the error msg so nothing comes up, but it fails to
correct it.

=IF('sheet2!F4="","",'sheet 2'!F3/'sheet2'!F4)

So if cell 'sheet2!F4 is blank, then nothing is input, otherwise process
F3/F4. To me, it looks like a simple process, but Excel doesnt like it.

If someone could shine some light on this, that would be much appreciated.

 

[Max]

Include a concurrent test with ISNUMBER
(to avoid spurious triggers where the source data may contain text)

Eg, for the green trigger, use Formula is:
=AND(ISNUMBER(A1),A1>=90)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik

 

[David Biddulph]

You're missing a quote mark (or you've got a spare one). And unless you've
got a sheet called sheet2 and another sheet called sheet 2, you need to be
consistent with the spaces.

 

[AT]

Thanks for the quick replies. Regarding the first reply from Max, I am unsure
where I would apply this. Would this be in the conditional formatting
criteria or is it to replace the formula that I posted up originally?

Anyway, I feel that I may have misrepresented my problem. The link is
between 2 sheets, so sheet 1 is where the formula is applied,
calculating/retrieving data from Sheet 2.

And finally, the critera for the conditional formatting to occur are linked
to cells which (as an example) contain the numbers 90 (Cell A1), 60 (Cell A2)
and 1 (Cell A3) (so that it's easy to adjust the variables). From here, I
setup the conditional formatting rules to state

Turn Green if Cell value is same or greater than Cell A1
Turn Yellow if Cell value Between A1 and A2
Turn Red if Cell value is Between A2 and A3

When I use the formula I mentioned in the first post, if there are numbers
in the cells it is pointing to (on a different sheet), after the simple
calculation, the cell will chage colour, corresponding to the value i.e. 49,
so the cell turns Red. However, when the cell that it is pointing to has no
value/entry, then it automatically changes Green rather than remain
blank/colourless.

I hope this makes it slightly clearer. Thanks for both individuals who have
made posts, I really appreciate it.

 

[Max]

Would this be in the conditional formatting criteria

or is it to replace the formula that I posted up originally?

The earlier suggestion's for use in the conditional formatting,
via the "Formula Is" option

With your further clarification, try it along these lines

In your sheet 2,

Assume you have the CF "limits" entered
in A1: 90
in A2: 60
in A3: 1

Then assume you want to conditionally format col C
Select col C (with C1 active)
Apply conditional formatting using the "Formula Is" option

Condition 1, Formula Is:
=AND(ISNUMBER(C1),C1>=$A$3,C1<$A$2)
Format: Red fill

Condition 2, Formula Is:
=AND(ISNUMBER(C1),C1>=$A$2,C1<$A$1)
Format: Yellow fill

Condition 3, Formula Is:
=AND(ISNUMBER(C1),C1>=$A$1)
Format: Green fill

Click to OK out

The above is a more robust way to apply the CF, and should clear up your issue
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---

 

[AT]

Thanks Max, works a treat.

Much appreciated!

AT

 

[Max]

Welcome, glad to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik