Reading of filename from cell

[Vinceaam]

Hi Everyone and thanks for any help in advance.

I have an excel spreadsheet in which I am linking to another spreadsheet. From this worksheet I have to return the SUM of an OFFSET. Because of the limitations of the OFFSET function it is ineffective if you change the linked workbook and the workbooks are closed.

I am therefore trying to use the INDIRECT function to retrieve the linked workbook file name which I have output to cell A1 in the relevant worksheet. the problem is that when I use this INDIRECT function it reads the "\" in the full file name as "|". I am not sure why this is occuring.

e.g.

Cell A1 = C:\Users\Vince\desktop\[test.xlsx]

but when i run the evaluate formula on the INDIRECT funtion it reads it as

INDIRECT("C:|Users|Vince|dektop|[test.xlsx])



Any assistance would be much appreciated.


Regards


Vince

 

[Firefly2012]

OK maybe you can use:

SUM(INDEX('C:\Users\Vince\desktop\[test.xlsx]NAV (Pool)'!$X:$X,MATCH(B64,'C:\Users\Vince\desktop\[test.xlsx]NAV (Pool)'!$J:$J,0)):INDEX('C:\Users\Vince\desktop\[test.xlsx]NAV (Pool)'!$X:$X,MATCH(B65,'C:\Users\Vince\desktop\[test.xlsx]NAV (Pool)'!$J:$J,0)-1))

 

[Vinceaam]

I have decided to pull in the sheet to the active workbook anyway. I required the historical data anyway.

Thanks for all your help