conditional formatting error message

[Guest]

I am writing a conditional format formula and am getting the error: "You may
not use references to other worksheets or workbooks for conditional
formatting" But, I'm not referring to another worksheet in my formula.

Here's the Conditional Format formula in G19:

=AND(G19="",(NETWORKDAYS(TODAY(),F19,holidays))>1)

What it does is make sure cell G19 isn't blank and then checks the number of
days between today and cell F19, minus my named range of 'holidays.' If
that's greater than 1, then turn it red.

What am I missing?

 

[Bob Phillips]

You cannot use NETWORKDAYS in conditional formatting. Why not add a helper
column to calculate the days, and use CF to test that difference.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks, Bob. Didn't know you couldn't use NETWORKDAYS in CF. Your
suggestion should work well.

 

[Biff]

The reason you get the error message is because the NETWORKDAYS function is
part of the Analysis ToolPak add-in which is "technically" in another
workbook/sheet. You can get around it by creating a named formula and then
use that named formula in the conditional formatting. Having said all that,
It would be less confusing/complicated to use Bob's suggestion.

Biff