Conditional formatting - source data from another sheet

[AT]

Hi, I currently have a lot of data on sheet 2, linking the content to sheet 1
for easy reference. I wish to to use conditional formatting on sheet 1 with
the original data remaining in sheet 2. Unfortunately, when i try to link the
cell through the application of conditional formatting, once I click ok, it
states that you cannot do this because the linked cell is on another sheet.

The easiest solution is probably to duplicate the appropriate cells on sheet
2 to sheet 1 with links, but I dont particularly want to duplicate data to
avoid confusion.

So, to put it simply, is there an easy solution to this simple problem?

Thanks in advance

 

[Gord Dibben]

You can set the CF on Sheet1 based on what is in a cell on Sheet1.

Why do you need to reference Sheet2 in your CF condition?

Assume A1 in Sheet1 has a linking formula to A1 in Sheet2

=Sheet2!A1

CF on the value returned by that formula.

If not the case, please show us the CF condition formula.


Gord Dibben MS Excel MVP

 

[Peo Sjoblom]

If you name the cell in the other sheet, select that particular cell, do
insert>name>define and type a name or select that cell and click in the name
box above Column A's header and type a name there, no spaces allowed and
certain letter number combinations are not allowed. Anyway assume you name
the cell MyName then you can use conditional formatting and formula is

=MyName>0

set a format and if the cell is greater than zero it will trigger the
formatting



--


Regards,


Peo Sjoblom

 

[Chriswilsonuk]

I have tried you example and it worked using a named cell.

Is there a way to get this to work on a range of cells or do I have to name
each individual cell. This could get very compolicated very fast with the
sheet I am using.

Thanks

Chris

 

[Ashish Mathur]

Hi,

Yes, you can name a range and then use conditional formatting across
worksheets. Could be very specific about the example.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com