Help with Indirect

[bark01]

I want to link to the same cell reference in a bunch of spreadsheets that are in month year folders.

I've created a formula that gives the text string I need in separate cell

In cell A1 i have a formula that generates the full text string i.e.
'\\customer\Invoices\Part Payment invoice level\2019\4\[Invoicing April 2019.xlsx]Sheet1'!$G$36

If in another cell if I type =then copy and paste the above text into it it and press return it brings back the right value so the logic i'm using to generate the text string seems good. As the text is variable dependant on row i want to link to it rather than copy and paste but every time i do i get a Ref error.

I thought I would just do =Indirect(A1). but that does refs ot and evaluating doesn't give me a hint as to whats wrong

 

[jasonb75]

Are the other files open?

Indirect will always show #REF if the source workbook is closed.

 

[bark01]

Well that was the issue, but the whole point of this was so i didn't have top open up all the sheets as there nearly 100 of them and most of them have passwords.

Is there another way to do this?

My current method is to take the text strings copy them into text pad do a find and replace to add an = to the start and then copy them back into excel. Not the most elegant solution!

 

[jasonb75]

Maybe copy and paste special > values (ctrl alt v, v) then find and replace (ctrl h) to change '// to ='//