Using a cell to create file name

[skatmandu2002]

Hiya,

Cell B2 in a file called 'Summary sheet' says:
="Cohort "&'[Details Sheet.xlsx]Home & Navigation'!$F$21
This means B2 displays 'Cohort D3'.

However, the name of Details sheet can be changed at any time... I was wondering, does this automatically update the formula in cell B2 of summary sheet?

Is there a way that I can type "Details Sheet" into cell A1, and then the formula in cells B2 can work off this? I have tried doing the following but it doesn't work:
="Cohort "&'["&A1&".xlsx]Home & Navigation'!$F$21


Hope that all made sense, I'm thinking it didn't?

Thanks!

[mdbct]

Wrap what you have in the INDIRECT function:

Please Login or Register  to view this content.

[skatmandu2002]

Hiya,

Thanks for replying. But it doesn't seem to work. I think it might be a problem with the speak marks (") but I can't seem to work it out.

any ideas?

[shg]

Try ="Cohort " & INDIRECT( "'[" & A1 & ".xlsx]Home & Navigation'!$F$21")

The linked workbook must be open in the same instance of Excel for INDIRECT to work.