Conditional formatting from other sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khoshravan - 14 Aug 2007 10:56 GMT

I am trying to make a following conditional formatting
Change the color of a cell in a range, if its value is maximum of the range.
The maximum value of the range is calculated through
Tools/Data/Analysis/Descriptive Statistics, which I have not reside in the
same sheet. But conditional formatting doesn’t accept to use reference from
other sheet. Is there any solution for this issue?
Also it doesn’t work when I put the formula Max(range) in the conditional
formatting reference box.

Reply to this Message

Max - 14 Aug 2007 11:28 GMT

One way is to use named ranges

Eg in Sheet2,
the CF formula used could be:
=B2=MAX(MyRange1)

where:
MyRange1 =Sheet1!$B$2:$B$7

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I am trying to make a following conditional formatting
> Change the color of a cell in a range, if its value is maximum of the range.
[quoted text clipped - 4 lines]
> Also it doesn’t work when I put the formula Max(range) in the conditional
> formatting reference box.

Reply to this Message

Khoshravan - 14 Aug 2007 11:50 GMT

Thanks Max but couldn't get your point exactly.
suppose that My original data is in sheet1. they already have names for ease
of work.
I have the Max and Min in another sheet (sheet2).
I don't want to recalculate them again in sheet1.
Using range name inside max function within CF didn't work.

I have to perform this task for may scenarios and it is not logical to
calculate my statistical valuse (max, min, ave, sd) tuwise.

Signature

Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

> One way is to use named ranges
>
[quoted text clipped - 12 lines]
> > Also it doesn’t work when I put the formula Max(range) in the conditional
> > formatting reference box.

Reply to this Message

Max - 14 Aug 2007 12:32 GMT

You could try INDIRECT then,
something like this as the CF formula in Sheet2:

=B2=INDIRECT("'Sheet1'!B3")
(assuming B2 is the active cell within the range to be CF'd in Sheet2)

where 'Sheet1'!B3 contains say, the calculated MAX value

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks Max but couldn't get your point exactly.
> suppose that My original data is in sheet1. they already have names for ease
[quoted text clipped - 5 lines]
> I have to perform this task for may scenarios and it is not logical to
> calculate my statistical valuse (max, min, ave, sd) tuwise.

Reply to this Message

Rate this thread: