Issue with INDIRECT function.
 

Hi All,

I have the following formula in cell A1 :
=NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine. But I am trying
to change this formula so that I use a target cell's value (DA2) to replace
the "FY09_Holidays" string in the above formula. So if 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!

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 using the networkdays function all over my
workbook.

BTW, when I insert the function =INDIRECT("DA2") in another cell, I do get
the "FY09_Holidays" value returned. so I am not sure what is going on when it
is part of the above networkdays function!

Can you please help?

Issue with INDIRECT function.
 

Try removing the " from around DA2

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


--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"LABKHAND" wrote:

Hi All,

I have the following formula in cell A1 :
=NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine. But I am trying
to change this formula so that I use a target cell's value (DA2) to replace
the "FY09_Holidays" string in the above formula. So if 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!

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 using the networkdays function all over my
workbook.

BTW, when I insert the function =INDIRECT("DA2") in another cell, I do get
the "FY09_Holidays" value returned. so I am not sure what is going on when it
is part of the above networkdays function!

Can you please help?

Issue with INDIRECT function.
 

Hi Paul,

I removed the quotes but i get #REF error.

"Paul" wrote:

Try removing the " from around DA2

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


--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"LABKHAND" wrote:

Hi All,

I have the following formula in cell A1 :
=NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine. But I am trying
to change this formula so that I use a target cell's value (DA2) to replace
the "FY09_Holidays" string in the above formula. So if 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!

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 using the networkdays function all over my
workbook.

BTW, when I insert the function =INDIRECT("DA2") in another cell, I do get
the "FY09_Holidays" value returned. so I am not sure what is going on when it
is part of the above networkdays function!

Can you please help?

Issue with INDIRECT function.
 

Using INDIRECT(DA2) works for me. I'm just guessing here, but do you have
quote marks around the text in DA2? If so, remove them.

--
Rick (MVP - Excel)


"LABKHAND" wrote in message
...
Hi Paul,

I removed the quotes but i get #REF error.

"Paul" wrote:

Try removing the " from around DA2

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


--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"LABKHAND" wrote:

Hi All,

I have the following formula in cell A1 :
=NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine. But I am
trying
to change this formula so that I use a target cell's value (DA2) to
replace
the "FY09_Holidays" string in the above formula. So if 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!

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 using the networkdays function all
over my
workbook.

BTW, when I insert the function =INDIRECT("DA2") in another cell, I do
get
the "FY09_Holidays" value returned. so I am not sure what is going on
when it
is part of the above networkdays function!

Can you please help?

Issue with INDIRECT function.
 

Hi Rick,

My current name range definition for FY09_Holidays is :
=OFFSET(FY2009Time!$DA$4,0,0,COUNT(FY2009Time!$DA: $DA),1)
If I change this definition to: =FY2009Time!$DA$4:$DA$13

and then remove the quotes from the INDIRECT("DA2") piece, then the code
works.
But my problem is that I can not change the named range definition to have
the exact location of start/end cells since some of my named range definition
have dynamic ranges. In another words, I need to keep the FY09_Holidays
named range definition to
=OFFSET(FY2009Time!$DA$4,0,0,COUNT(FY2009Time!$DA: $DA),1) which then breaks
the code! I have tried many things and I can not figure this out.

I need to have the COUNT(...) function as part of my name rnage definition.
Thanks


"Rick Rothstein" wrote:

Using INDIRECT(DA2) works for me. I'm just guessing here, but do you have
quote marks around the text in DA2? If so, remove them.

--
Rick (MVP - Excel)


"LABKHAND" wrote in message
...
Hi Paul,

I removed the quotes but i get #REF error.

"Paul" wrote:

Try removing the " from around DA2

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


--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"LABKHAND" wrote:

Hi All,

I have the following formula in cell A1 :
=NETWORKDAYS($CK3,$CK3+14,FY09_Holidays) which works fine. But I am
trying
to change this formula so that I use a target cell's value (DA2) to
replace
the "FY09_Holidays" string in the above formula. So if 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!

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 using the networkdays function all
over my
workbook.

BTW, when I insert the function =INDIRECT("DA2") in another cell, I do
get
the "FY09_Holidays" value returned. so I am not sure what is going on
when it
is part of the above networkdays function!

Can you please help?


.