Conditional Formatting

[Rob33]

is it possible to conditional format a cell on one tab - but using a reference from another tab to format the cell eg:

I want cell A-1 (sheet2) to change colour if cell A-1(sheet1) is = 1

 

[Brian from Maui]

is it possible to conditional format a cell on one tab - but using a reference from another tab to format the cell eg:

I want cell A-1 (sheet2) to change colour if cell A-1(sheet1) is = 1

If you "name" sheet1!A1. Then use formula is in sheet2A1 =name=1. If you have a lot of cells, it may not be practical to name all.

 

[The Gunslinger]

if you do not name the cell but go through the conditional formatting selection from the menu, select "formula is" then select the sheet (tab) you want the cell to look at and then select the cell you want to control the format (colour) and type "=1" after the cell reference automatically entered ..... be aware of these points

the arrow keys will not work as you expect in the formula bar

the range selected must be a single cell reference style ..... if you get an entry like "A1:C1" ..... you must delete the ":C1" part before typing "=1" or it will not work

hope this helps

The Gunslinger

 

[Yogi Anand]

if you do not name the cell but go through the conditional formatting selection from the menu, select "formula is" then select the sheet (tab) you want the cell to look at and then select the cell you want to control the format (colour) and type "=1" after the cell reference automatically entered ..... be aware of these points

the arrow keys will not work as you expect in the formula bar

the range selected must be a single cell reference style ..... if you get an entry like "A1:C1" ..... you must delete the ":C1" part before typing "=1" or it will not work

hope this helps

The Gunslinger

Hi Gunslinger:

For Conditional Formating, EXCEL does not allow one to base one's formating on a cell in another sheet -- that is why Brian in his post above had recommended creating a named range -- and that would be acceptable to EXCEL.

For some special situations, I can make use of the INDIRECT function to refer to a cell in another sheet. So, if I am applying Conditional Formating to a cell in sheet1, and I want to base my formating on cell A5 in sheet2 being =3, then, for Conditional Formating for a cell in sheet1, I would be able to use ...

Formula is ... =INDIRECT("Sheet2!$A$5")=3

However, generally speaking the named range approach is the one I would take.

 

[The Gunslinger]

ahhhh ... i stand corrected, my appologies ..... i tend to use named ranges too now most of the time and i didnt realise it would not accept it the way i suggested .... always good to learn something new

The Gunslinger