Forum Discussion
Add values from same cell in corresponding worksheet in other closed workbooks?
We are using Excel to keep track of and schedule clinic procedures. Each surgeon has a file/workbook and each month is a different worksheet (named by first day of each month). Each day has a running total of cases as they are added and I want to sum the cases of all the surgeons on each day. Each workbook will be open on a different computer, but saved on a common network folder. My problem is getting the reference to the worksheet to update with each new worksheet added. I've managed to get it to work with INDIRECT using a variable that refers to a cell that has it's worksheet name in it, but only if all the worksheets are open at the same time.
Is there a way to do this? (I've tried VLOOKUP and SUM, but can't get a variable referring to the worksheet to work.)
Thank you!
Dan
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!
4 Replies
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 SubIf 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!
