Forum Discussion
Add values from same cell in corresponding worksheet in other closed workbooks?
Typically you would use the INDIRECT() function to accomplish this however this function won't work if the workbook you want to pull data from is closed. You're running into a software limitation for native functions. The only other way to accomplish your task would probably be to write VBA code or use a third party add-in.
I found a few online references on how you might go about this (see the bottom of the post) However, depending on the complexity of your issue, I would make a "catch all data worksheet" with formulas (full path formulas without variables) for all of the data you need pulling into your workbook. Then just reference this sheet instead of using variables... or possible rework the current workbooks on the network drive to work better for your purposes.
Here are the links I found:
http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/
https://stackoverflow.com/questions/21072781/use-file-path-from-a-predefined-cell-in-a-formula#comment31693600_21072823
Hope this helps!
I'm having trouble using a variable to identify the sheet. All the files are in the same folder on the same network drive.
Each workbook has a sheet for each 4 week block so I'm tried to get the sheet name in a cell and then refer to that cell as the sheet name when I specified the in the path/workbook/sheet, but I haven't been able to get it to work.
Could you help me with that syntax?
Typically you would use the INDIRECT() function to accomplish this however this function won't work if the workbook you want to pull data from is closed. You're running into a software limitation for native functions. The only other way to accomplish your task would probably be to write VBA code or use a third party add-in.
I found a few online references on how you might go about this (see the bottom of the post) However, depending on the complexity of your issue, I would make a "catch all data worksheet" with formulas (full path formulas without variables) for all of the data you need pulling into your workbook. Then just reference this sheet instead of using variables... or possible rework the current workbooks on the network drive to work better for your purposes.
Here are the links I found:
http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/
https://stackoverflow.com/questions/21072781/use-file-path-from-a-predefined-cell-in-a-formula#comment31693600_21072823
Hope this helps!
- Thank you Matt! I'm limited in my ability to change the format of the workbooks, but I am going to try using another workbook for reference. I really appreciate your help!