How to reference cells from a closed workbook with a formula

Hi forum,

I have been unable to find an answer for this but is there a way to reference a cell from a closed workbook?
I know that you can use INDIRECT to reference from an open workbook but that doesn't seem to work with closed workbooks and just produces #REF.

Is there a way? Please see attached documents for a VERY SIMPLE Mockup.

EDIT: For reference, the actual (not Mockup) closed workbook is on Microsoft SharePoint is this changes anything.

Kind Regards,

Julian

Your example may be just too simple. If ALL you want to do is draw A2 from the closed sheet:

='[closedbook.xlsx]Sheet1'!$A$2

and refresh links when prompted. But... it really depends on the other bits you haven't told us about yet!!

Ok let me try that.
I just wanted to get the bare bones working first before I went further.
EDIT: Ok that works. So strange that when I try something like that i get #REF or "this link could not be found" or words to that effect.

Ok so the advanced level is that the workbook name and sheet name changes.
There are 33 total workbooks with different names and sheet names.
The Workbook name and sheet name are the same
Example:
Workbook name: result_wget_Sheet1.csv
Sheet Name: result_wget_Sheet1

The MS SharePoint location is the following: https://XXXXgroup.sharepoint.com/sites/114470/Shared Documents/1.Docs In/XXXX/HC data/12 2023/result_wget_Sheet1.csv
Where the bits in red are the confidential bits.

I initially tried INDIRECT of e.g. 'https://XXXXgroup.sharepoint.com/sites/114470/Shared Documents/1.Docs In/XXXX/HC data/12 2023/[result_wget_Sheet1.csv]result_wget_Sheet1'!A2 as the goal so building like
A2 = 'https://XXXXgroup.sharepoint.com/sites/114470/Shared Documents/1.Docs In/XXXX/HC data/12 2023/[result_wget_
B2 = SheetX
B3 = .csv]result_wget_&B2
B4 = '!&A2
B5 = INDIRECT(A2&B2&B3&B4)
But this didn't work for some reason. Sorry it's difficult to explain. The end result is this indirect does not work with closed workbooks.

So basically what changes is the SheetX part of both the parts of the Sharepoint highlighted in blue

Is it possible to build this formula dynamically so that i could drag it down so that each part updates based on e.g. Sheet A of workbook A, then next row down it references Sheet B of workbook B, Sheet C of workbook C and so on.
I'll Mockup some workbooks.

Your explanation WAS too simple. Basically, there is NO good, easily applicable formula that can pick up file names or csheet names from cells (the bits thta you didn't tell us about, as I suspected!!).

Best to stick with VBA, if that can be tailored to meet your needs.