linking cells to mutliple worksheets.

[AndrewC]

I have 2 workbooks, one with multiple worksheets the other with just single
worksheet that links back to the other to get data, my problem is that I want
the linking to be semi-automatic I guess you could call it, here is an
example of what i mean:
Cell B4 ='[name of file.xls]A4'!$K$4
The "A4" I want to be a cell so then the next row would be:
Cell B5 ='[name of file.xls]A5'!$K$4
This way I only have to change the input in Column A
Any ideas?

 

[Arvi Laanemets]

Hi


You can use INDIRECT
B4=INDIRECT("'[name of file.xls]" & A4 & "'!$K$4")
, but there is a drawback - the formula works only, when the source file is
opened.