Cell Referencing

[MadHatsJess]

Hi

I have a central spreadsheet that will be fed by around 20 identical reports (apart from the data).

I am using the simple formula below to pull it together (will be refreshed monthly and the file names will be the same)

='C:\File path\[Report 1.xlsx] Main Report Report '!$C$9
='C:\File path\[Report 2.xlsx] Main Report Report '!$C$9

Is there away to reference the bold part of the formula to a cell in excel with the file name?

This would save me about 10 hours :D

Thanks in advance

Jess

 

[Special-K99]

=INDIRECT("'C:\File path"&A1&" Main Report Report '!$C$9")

where A1 is the name of the file

 

[jorismoerings]

Hi,

Take a look at the INDIRECT function.
many example on the net available, eg http://www.contextures.com/xlFunctions05.html