Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Indirect and Path & File Names
Hi
I have the following formula that works a treat if all my 80 Workbooks are open SUMPRODUCT((INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to 2005_03_31.xls]Sheet1'!$E$5:$E$10000")'Sales'!C9)*(INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to 2005_03_31.xls]Sheet1'!$a$5:$a$10000")<'Sales'!$H$6)) Is there a way to arrange the above formula to include the path of the relevent files so they do not need to be open? The path is C:\Workbooks\<filename TIA |
#2
|
|||
|
|||
Hi Anthony
I think I'm right in saying that the INDIRECT function will only work across workbooks if the workbooks are actually open. I've used a similar technique using the OFFSET function and I always needed the books open. Nick "Anthony Slater" wrote in message ... Hi I have the following formula that works a treat if all my 80 Workbooks are open SUMPRODUCT((INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to 2005_03_31.xls]Sheet1'!$E$5:$E$10000")'Sales'!C9)*(INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to 2005_03_31.xls]Sheet1'!$a$5:$a$10000")<'Sales'!$H$6)) Is there a way to arrange the above formula to include the path of the relevent files so they do not need to be open? The path is C:\Workbooks\<filename TIA |
#4
|
|||
|
|||
Don
Thanks for that, I've been pulling my hair out over that one.. So now the question: - How can I fit a named range in this formula? (I'm not a user of Named ranges) "Don Guillett" wrote: Indirect does NOT work with closed workbooks. Try using named ranges. -- Don Guillett SalesAid Software "Anthony Slater" wrote in message ... Hi I have the following formula that works a treat if all my 80 Workbooks are open SUMPRODUCT((INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to 2005_03_31.xls]Sheet1'!$E$5:$E$10000")'Sales'!C9)*(INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to 2005_03_31.xls]Sheet1'!$a$5:$a$10000")<'Sales'!$H$6)) Is there a way to arrange the above formula to include the path of the relevent files so they do not need to be open? The path is C:\Workbooks\<filename TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a closed file | Excel Worksheet Functions |