conditional formating based on value in different worksheet

[novicehacker]

I want to use conditional formatting to turn a cell yellow. However the cell which i want to test is on another worksheet, and Excel gives me an error "You may not use references to other worksheets or other workboots for conditional formatting criteria" is there some way i can get around this?

 

[GorD]

Re: conditional formating based on value in different worksh

The only way round this (I know) is to use a named range as your reference.

 

[Zack Barresse]

yes, GorD has it.

name your range on one sheet (xl 03 is best for this)
in the cell you want changed, CF, formula is:
=YourNamedRange<>""

or whatever check you wish to do. worked for me.

HTH

 

[bjwillingham]

Re: conditional formating based on value in different worksh

It appears the other spreadsheet must be open for the format to work if using named ranges. However, if you have a 'cellx' (perhaps on a hidden tab) that directly references the speadsheet with the conditional format referencing that 'cellx', the format works even if the other spreadsheet is not opened.

 

[GorD]

Re: conditional formating based on value in different worksh

The exam question was related to another worksheet, not another workbook, but Ibelieve you are correct

 

[bjwillingham]

Re: conditional formating based on value in different worksh

Oops! My bad. Three slaps on the wrist.

Well, maybe someone as confused as me will learn something.

 

[Zack Barresse]

Re: conditional formating based on value in different worksh

ya, me! i thought i was reading this all wrong! not enough coffee yet

btw, i've donemuch worse...