I want to access another document using a filepathlink formula but take part of the name from a cell

[tonywatsonhelp]

Hi Everyone,
Currently in cell H2 I have the following formula
=SUM('https://hcr.sharepoint.com/sites/Finance/Shared Documents/Finance - Confidential/Accounts/Mgmt Accounts/Project Reports/2022_Proj/21122001/[21122001_Dashboard.xlsm]Sales'!$G:$G)
which works great at pulling in a figure from this other sheet.
What I want to do is instead of having the number "21122001" in the formula, have it in cell B2 and look it up, but everything I'm trying doesn't seam to work,
any ideas how i could get this to work?
Thanks
Tony

 

[PeteWright]

You can use INDIRECT, but it will only work, if the linked document is open!

220723.xlsm
	A
1	3
2	4
Sheet1

Book1.xlsm
	A	B
1	Path_A	https://d.docs.live.net/b94bc0f382aae2aa/Desktop/
2	Path_B	test/2022-07-23
3	Path	https://d.docs.live.net/b94bc0f382aae2aa/Desktop/test/2022-07-23/220723.xlsm
4	Filename	220723.xlsm
5
6	7
7	7
Sheet6

Cell Formulas
Range	Formula
B3	B3	=B1&B2&"/"&B4
A6	A6	=SUM(INDIRECT("'"&B1&B2&"/["&B4&"]Sheet1"&"'!A1:A2"))

If you don't want to open the workbook you can use VBA to do the job:

Sub getDataFromWB()
   Dim Path_A, Path_B, Path_X, FileNameWB As String
   Dim WB As Workbook
   Dim Sum As Double
   Application.ScreenUpdating = False
   Path_A = ThisWorkbook.Sheets("Sheet6").Range("B1")
   Path_B = ThisWorkbook.Sheets("Sheet6").Range("B2")
   FileNameWB = ThisWorkbook.Sheets("Sheet6").Range("B4")
   Path_X = Path_A & Path_B & "/" & FileNameWB
   Set WB = Workbooks.Open(Path_X)
   Sum = WorksheetFunction.Sum(WB.Sheets("Sheet1").Range("A1:A2"))
   ThisWorkbook.Sheets("Sheet6").Range("A7").Value2 = Sum
   WB.Close savechanges:=False
   Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Thank you, the VBA was perfect