Conditional formatting from other sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
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
 
Back
Top