I’m trying to access a set of data on another workbook with only one manual link
The data is always in the same cells
But we use many different sheets to a workbook, and many different files/workbooks

I have something that nearly works. The problem I believe may come from some form of circular reference.
I’ve tried to explain the whole process to give a bit more understanding.

Any help appreciated!



I have 2 files – file1 and file2

File1 contains the following code:

=INDIRECT("'" & $I22 & "'!A4")

Cell I22 (file 1) I want to link to a cell in file2 which is the file name and sheet name of this referenced cell in file2

So I manually link file1 I22 to file2 cell A1

Cell A1 contains the data [file2.xlsx]Sheet1 – this is then used by the INDIRECT function to take information from file2/sheet1/cell A4

If I manually enter the following data into file2 cell A1 everything works fine
[file2.xlsx]Sheet1

I have problems when I attempt to use a function in file2 to display the same information
=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))

This gives the initial result as expected
[file2.xlsx]Sheet1

After re-calculating once however, the cell in sheet2 displays
[file1.xlsx]Sheet1

What is going wrong here? Thanks in advance.