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!
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!