| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| use of the indirect function? | Excel Discussion (Misc queries) | |||
| Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
| INDIRECT Formula with an IF Then issue | Excel Worksheet Functions | |||
| INDIRECT.EXT issue | Excel Discussion (Misc queries) | |||
| INDIRECT function inside AND function | Excel Worksheet Functions | |||
Linear Mode
