conditional formats using named references

[JBinkert]

For Example:
I define a local name for a partial column (ie G:3 to G:50) on worksheet tab
"INC" as "INC!BDGT"

In column H ("REV"), I want to apply a conditinal format formula to change
the cell format background color when the REV number is not equal to the BDGT
number.

I can't seem to get the conditional format functions to evaluate the
relative value of named reference "BDGT". instead it treats the named
reference as text and inserts quotes. I've tried forcing an evaluation with
square brackets (ie [BDGT]) but that doesn't work either.

Any suggestions or is named relative references not permitted in the
conditional format evaluation formulas?

 

[Chip Pearson]

Don't use the sheet name prefix to the name in the conditional
formatting formula. Just reference the name and the name that is local
to the sheet containing the conditional formatting will be used. For
example,

=H1<>BDGT

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JBinkert]

Thanks Chip but it doesn't seem to work.

When I try your example I can make it work for the first cell in the range.
First the conditional format thinks I want to evaluate to the text "BDGT" and
then when I remove the quotes it works for the one cell. when I copy and
'paste special'> "formats", it doesnt work. ie even when the condition is
false the format is applied.

When I try to apply conditional formatting to the range ("REV") all at once,
the formatting results arent incorrect. ie I cant seem to evaluate relative
REV<>BDGT as a condition.

Did you try your method for named ranges of cells in a worksheet?

want to email me the example?

(e-mail address removed)

Don't use the sheet name prefix to the name in the conditional
formatting formula. Just reference the name and the name that is local
to the sheet containing the conditional formatting will be used. For
example,

=H1<>BDGT

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

For Example:
I define a local name for a partial column (ie G:3 to G:50) on worksheet tab
"INC" as "INC!BDGT"

In column H ("REV"), I want to apply a conditinal format formula to change
the cell format background color when the REV number is not equal to the BDGT
number.

I can't seem to get the conditional format functions to evaluate the
relative value of named reference "BDGT". instead it treats the named
reference as text and inserts quotes. I've tried forcing an evaluation with
square brackets (ie [BDGT]) but that doesn't work either.

Any suggestions or is named relative references not permitted in the
conditional format evaluation formulas?