conditional formatting error message

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karla V - 30 Oct 2006 18:24 GMT

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?

Reply to this Message

Bob Phillips - 30 Oct 2006 19:56 GMT

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

Signature

HTH

Bob Phillips

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

> I am writing a conditional format formula and am getting the error: "You may
> not use references to other worksheets or workbooks for conditional
[quoted text clipped - 9 lines]
>
> What am I missing?

Reply to this Message

Karla V - 30 Oct 2006 20:03 GMT

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

> You cannot use NETWORKDAYS in conditional formatting. Why not add a helper
> column to calculate the days, and use CF to test that difference.
[quoted text clipped - 14 lines]
> >
> > What am I missing?

Reply to this Message

Biff - 31 Oct 2006 04:37 GMT

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

> Thanks, Bob. Didn't know you couldn't use NETWORKDAYS in CF. Your
> suggestion should work well.
[quoted text clipped - 21 lines]
>> >
>> > What am I missing?

Reply to this Message