Issue with INDIRECT function.

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?
 
R

Rick Rothstein

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.
 
L

LABKHAND

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top