Issue with INDIRECT function.

  • Thread starter Thread starter LABKHAND
  • Start date Start date
L

LABKHAND

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?
 
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.
 
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
 
Back
Top