#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default NETWORKDAYS

When using conditional formating i get the following message when trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
! any ideas why this would be the case

many thanks

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default NETWORKDAYS

It suggest NETWORKDAYS is referencing data in a worksheet other than the one
in which you have the CF.

You can get round this by using named ranges and referencing these in you CF
formulae.

HTH

"Suddes" wrote:

When using conditional formating i get the following message when trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
! any ideas why this would be the case

many thanks

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default NETWORKDAYS

I'd think it's because the NETWORKDAYS function, prior to XL07, exists
in the Analysis Toolpak Add-in, which is an "other...workbook".

In article ,
Suddes wrote:

When using conditional formating i get the following message when trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
! any ideas why this would be the case

many thanks

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default NETWORKDAYS

=networkdays() is part of the analysis toolpak (in xl2003 and below).

Since not everyone may have that addin loaded, I'm guessing that excel wants to
make sure conditional formatting doesn't break.

(I sometimes use an adjacent cell (and hide the column). And put the formula
there. Then use that cell in the CF rule.



Suddes wrote:

When using conditional formating i get the following message when trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
! any ideas why this would be the case

many thanks


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default NETWORKDAYS

Thanks Dave and all, I do have the analysis tool pack loaded on my source
spread sheet.
Can you give me an example of how i would include the hidded colunm in my
formula.

thanks

"Dave Peterson" wrote:

=networkdays() is part of the analysis toolpak (in xl2003 and below).

Since not everyone may have that addin loaded, I'm guessing that excel wants to
make sure conditional formatting doesn't break.

(I sometimes use an adjacent cell (and hide the column). And put the formula
there. Then use that cell in the CF rule.



Suddes wrote:

When using conditional formating i get the following message when trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
! any ideas why this would be the case

many thanks


--

Dave Peterson



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default NETWORKDAYS

I think he means that rather than referring to the other workbook in the
main formula, in another column in the same workbook add a reference to the
cells in the other workbook, such as

='[workbook name.xls]sheet name'!A1

and then in your formula use this cell.

You can hide that column (FormatColumnsHide) if you don't want to see it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Suddes" wrote in message
...
Thanks Dave and all, I do have the analysis tool pack loaded on my source
spread sheet.
Can you give me an example of how i would include the hidded colunm in my
formula.

thanks

"Dave Peterson" wrote:

=networkdays() is part of the analysis toolpak (in xl2003 and below).

Since not everyone may have that addin loaded, I'm guessing that excel
wants to
make sure conditional formatting doesn't break.

(I sometimes use an adjacent cell (and hide the column). And put the
formula
there. Then use that cell in the CF rule.



Suddes wrote:

When using conditional formating i get the following message when
trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for
conditional
formatting criteria"
! any ideas why this would be the case

many thanks


--

Dave Peterson



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default NETWORKDAYS

Just to add to what Bob wrote...

Say you want to apply format|Conditional formatting to A1.
Put your =networkdays() formula in B1
Then use B1 as a reference in the Format|conditional formatting rule:

Formula is:
=$B$17
(or whatever you wanted)

Suddes wrote:

Thanks Dave and all, I do have the analysis tool pack loaded on my source
spread sheet.
Can you give me an example of how i would include the hidded colunm in my
formula.

thanks

"Dave Peterson" wrote:

=networkdays() is part of the analysis toolpak (in xl2003 and below).

Since not everyone may have that addin loaded, I'm guessing that excel wants to
make sure conditional formatting doesn't break.

(I sometimes use an adjacent cell (and hide the column). And put the formula
there. Then use that cell in the CF rule.



Suddes wrote:

When using conditional formating i get the following message when trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
! any ideas why this would be the case

many thanks


--

Dave Peterson


--

Dave Peterson
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default NETWORKDAYS

Thanks for all your help i shall give it a go

thanks again

"Dave Peterson" wrote:

Just to add to what Bob wrote...

Say you want to apply format|Conditional formatting to A1.
Put your =networkdays() formula in B1
Then use B1 as a reference in the Format|conditional formatting rule:

Formula is:
=$B$17
(or whatever you wanted)

Suddes wrote:

Thanks Dave and all, I do have the analysis tool pack loaded on my source
spread sheet.
Can you give me an example of how i would include the hidded colunm in my
formula.

thanks

"Dave Peterson" wrote:

=networkdays() is part of the analysis toolpak (in xl2003 and below).

Since not everyone may have that addin loaded, I'm guessing that excel wants to
make sure conditional formatting doesn't break.

(I sometimes use an adjacent cell (and hide the column). And put the formula
there. Then use that cell in the CF rule.



Suddes wrote:

When using conditional formating i get the following message when trying to
use NETWORKDAYS' function

"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
! any ideas why this would be the case

many thanks

--

Dave Peterson


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
NETWORKDAYS [email protected] Excel Discussion (Misc queries) 2 November 21st 06 03:06 PM
NETWORKDAYS Connie Martin Excel Worksheet Functions 3 July 28th 06 07:52 PM
networkdays kevt Excel Worksheet Functions 1 September 8th 05 02:23 PM
NETWORKDAYS sinbad Excel Worksheet Functions 2 July 19th 05 12:32 PM
Networkdays Susan Hayes Excel Worksheet Functions 1 April 5th 05 06:33 PM


All times are GMT +1. The time now is 07:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"