Trouble using INDIRECT function

[stilton]

I've been able to use INDIRECT in the past but not in the current context and not sure if this is possible. I have 700 worksheets in a separate folder named using numeric numbers 1-700 i.e. 252.xlsx which are all identical except the data in each cell value. I created a workbook to recap specific cell data in each which I can reference directly but if I decide to add another cell reference that's a lot of additional work and would like to make it a relative link.

The file(s) to reference are located at K:\GPM\Audit Projections\SAAR Archive\2015\Jun and I've hardcoded using ='K:\GPM\Audit Projections\SAAR Archive\2015\Jun\[232.xlsx]Prelim'!$H$5 and works but can't seem to use INDIRECT to replace [232.xlsx] for some reason.

I've tried =INDIRECT("'K:\GPM\Audit Projections\SAAR Archive\2015\Jun\[" & A5 & ".xlsx]Prelim'!$H$5") where cell A5 contains the number 7 which should equate to 7.xlsx but only shows a #ref error...I'd appreciate any help someone can provide!

 

[Jonmo1]

Unfortunately, Indirect doesn't work on closed workbooks.

There is an addin called MoreFunc that has an indirect equivalent that will.
Search google for MoreFunc Addin
You'll have to sort through a bunch of adds and junk to find it though..

 

[stilton]

Good to know...thanks for the info! I did find it but unfortunately it didn't have the Indirect function included - several others I'll use but not that one in particular. For now I'll have to resolve to hard coding and maybe find another solution down the road.