Get cell content from another sheet of the same name in another workbook

[austind72]

Hi all - I have two workbooks open with two sheets of the same name.

Eg;

workbook1.xlsx
workbook2.xslx

Both have a sheet called 'mysheet1'

I'd like to get the value of A1 from workbook2.xlsx 'mysheet' and display it in workbook1.xlsx

Any help appreciated :^}

[BadlySpelledBuoy]

=[Woorkbook2.xlsx]mysheet1!A1

Note both workbooks should be open for this to work. If you then close Workbook2.xlsx the formula will update to show the full file path too.

BSB

[austind72]

Thanks - is there a way I can do it dynamically?

I'll be using it in lots of different sheets but they'll all have a corresponding sheet in the other workbook.

I'm using this formula to get the worksheet name:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

If I place that in cell B1:

=[workbook2.xlsx]B1!A1

I get a message saying there's a problem