Prev: pivot problem
Next: Userform combobox matchrequired = True; error with no selectio
From: LABKHAND on 15 Jan 2010 09:06
Hi All,

I have the following formula in cell A1:
=NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine.

I am trying to change this formula so that I can use a target cell's value
(e.g., DA2) to replace the "FY09_Holidays" string in the above networkdays
formula. So if cell DA2 has the value of FY09_Holidays, then I tried using
the INDIRECT function in cell A1 this way:

=NETWORKDAYS($CK3,$CK3+14,INDIRECT("DA2")).

This gives me a value error! Some sort of wrong data type error!

I also tried removing the quotes around DA2, but it still did not work.

If this function works, my code will be very flexible for the following
years since I can just change the value of DA2 cell to "FY10_Holidays"
without a need to change the cell formulas which use the networkdays function
all over my workbook.

I appreciate your help.
From: RonaldoOneNil on 15 Jan 2010 10:11
Works fine on mine - without the quotes is the correct syntax.
=NETWORKDAYS($CK3,$CK3+14,INDIRECT(DA2))

I get #Value error if one of the cells in my defined range FY09_Holidays is
not a valid date.

"LABKHAND" wrote:

> Hi All,
>
> I have the following formula in cell A1:
> =NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine.
>
> I am trying to change this formula so that I can use a target cell's value
> (e.g., DA2) to replace the "FY09_Holidays" string in the above networkdays
> formula. So if cell DA2 has the value of FY09_Holidays, then I tried using
> the INDIRECT function in cell A1 this way:
>
> =NETWORKDAYS($CK3,$CK3+14,INDIRECT("DA2")).
>
> This gives me a value error! Some sort of wrong data type error!
>
> I also tried removing the quotes around DA2, but it still did not work.
>
> If this function works, my code will be very flexible for the following
> years since I can just change the value of DA2 cell to "FY10_Holidays"
> without a need to change the cell formulas which use the networkdays function
> all over my workbook.
>
> I appreciate your help.
From: Chip Pearson on 15 Jan 2010 11:46
Get rid of the quotes around DA2. E,.g

=NETWORKDAYS($CK3,$CK3+14,INDIRECT(DA2))

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Fri, 15 Jan 2010 06:06:01 -0800, LABKHAND
<LABKHAND(a)discussions.microsoft.com> wrote:

>Hi All,
>
>I have the following formula in cell A1:
>=NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine.
>
>I am trying to change this formula so that I can use a target cell's value
>(e.g., DA2) to replace the "FY09_Holidays" string in the above networkdays
>formula. So if cell DA2 has the value of FY09_Holidays, then I tried using
>the INDIRECT function in cell A1 this way:
>
>=NETWORKDAYS($CK3,$CK3+14,INDIRECT("DA2")).
>
>This gives me a value error! Some sort of wrong data type error!
>
>I also tried removing the quotes around DA2, but it still did not work.
>
>If this function works, my code will be very flexible for the following
>years since I can just change the value of DA2 cell to "FY10_Holidays"
>without a need to change the cell formulas which use the networkdays function
>all over my workbook.
>
>I appreciate your help.
 | 
Pages: 1
Prev: pivot problem
Next: Userform combobox matchrequired = True; error with no selectio