Conditional formatting from other sheet

K

Khoshravan

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.
 
M

Max

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
 
K

Khoshravan

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.
 
M

Max

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
 
Top