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!
You can read values from a closed workbook on a network drive (As long as the user is connected to the drive) by using the absolute path. For example you would need to use this syntax:
='Z:\Test Folder\[Book2.xlsx]Sheet1'!A3+'Z:\Test Folder\[Book1.xlsx]Sheet1'!A3
or if all users don't have the drive mapped to the same letter you can use the UNC Path:
='\\ns-hjykvfs01\Server Name Here\Test Folder\[Book1.xlsx]Sheet1'!A3
To get the UNC Path of the file on the network you can use this VBA code while you are in a network file:
Sub GetUNC()
'You will need to go to the Visual Basic Editor (VBE) in Excel and Paste this code
'You will also need to add a reference to the Microsft Scripting Runtime Object Library
'In the VBE Go to > Tools > References > Select 'Microsft Scripting Runtime'
'Click OK
currentFile = ActiveWorkbook.FullName
Set objFSO = CreateObject("Scripting.FileSystemObject")
currentDrive = objFSO.GetDriveName(currentFile)
Set colDrives = objFSO.Drives
For Each objDrive In colDrives
If objDrive.DriveType = 3 Then
If StrComp(currentDrive, objDrive.DriveLetter & ":", 1) = 0 Then
currentFile = Replace(currentFile, currentDrive, objDrive.ShareName, 1, 1, 1)
End If
End If
Next
MsgBox currentFile
End Sub
If you are unfamiliar with how to use this code you can use this tutorial:
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/
- Thank you Matt!
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!
