Formatting a cell with a function (IF statement)

[Guest]

I have a matrix of values in sheet 1, and the cells in sheet 2 refer to sheet
1 with:
=IF('Sheet1'!A1>1400,"X"," ")
but instead of outputting an X in the cell in sheet 2, I would like the cell
to turn red if the corresponding cell in sheet 1 is >1400.

Help!

 

[Biff]

Hi!

You need to use conditional formatting to accomplish this. Because the
criteria is established on a different sheet you cannot directly reference
Sheet1!A1 in the formula needed. This could make copying the conditional
formatting to other cells in the "matrix" slightly more complicated.

Select the cell in Sheet2 that you want to format based on
=IF('Sheet1'!A1>1400

Goto Format>Conditional Formatting
Formula is: =INDIRECT("Sheet1!A1")>1400
Click the Format button and select the style(s) you want
OK out

Another way is to name the cell on Sheet1

Insert>Name>Define
Name: Sh1A1
Refers to: =Sheet1!$A$1

Then, the cf Formula is: =Sh1A1>1400

Biff

 

[Vasant Nanavati]

I assume you want the background to turn red.

Skip the formula. Using the Name Box, assign a range name (say "Rng") to
Sheet1!A1. With the cell in Sheet2 selected, use:

Format | Conditional Formatting | Formula Is | =Rng>1400 | Format | Patterns
| Cell Shading | Color | Red | OK | OK