Variable filename, referenced from another cell

[christenprochaska]

This seems like it should be pretty straightforward but after searching online for an hour I have yet to find a solution that will work for me. Please help!

I have several spreadsheets: A "Master.xls", and numbered spreadsheets ("1.xls", "2.xls", etc.)

On the Master spreadsheet I have the following:
Col. A Col. B
R1 File # Owner
R2 1 ='[1.xlsx]Sheet1'!$B$2

(On the numbered spreadsheets, in cell B2 I have the owner listed.)

What can I change the manually typed "1" to to make it reference cell C2?
='[1.xlsx]Sheet1'!$B$2

I would like this formula in cell B2 (of the Master spreadsheet) to be "smart enough" to reference cell A2 because this "formula" will be used a lot and we will need to manually change the filename on a lot of rows if we can't figure it out.

Thank you in advance!

[Kevbw]

Hi not sure what you really want to do.....but what you could do is leave R2 as is.....in R3 enter 2 then right click on the 2 and click hyperlink, you will get a dialogue box open on the left click where you want the link to go IE this workbook, click on the sheet you want the link to goto, in the type cell reference box type C2 then click ok then when you click on the number 2 you will be taken to the sheet and cell you want to go to.

hope this is of some help