How can I do this..

[Richard M Burton]

I have a formula, ='G:\Salaries\[01a Salaries
Apr07.xls]Data'!smithj, that pulls data from a separate workbook and works
fine.

But I would like to make this formula copyable across rows and columns. I
have cells with the different dates (as text) across the top of my sheet and
the different names (as text) down the side.

Any suggestions?

Thanks
Richard

 

[Bernard Liengme]

In B2 with the text "Apr07" in B1 and "smithj" in A2
=INDIRECT("'G:\Salaries\[01a Salaries "&B1&".xls]Data'!"&A2)
best wishes

 

[Dave Peterson]

The bad news is that Bernard's suggestion to use =indirect() requires that the
sending workbook be open. And then you wouldn't need the drive/path information
in the formula.

But the good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that allows you to return values from closed
workbooks.

In B2 with the text "Apr07" in B1 and "smithj" in A2
=INDIRECT("'G:\Salaries\[01a Salaries "&B1&".xls]Data'!"&A2)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I have a formula, ='G:\Salaries\[01a Salaries
Apr07.xls]Data'!smithj, that pulls data from a separate workbook and works
fine.

But I would like to make this formula copyable across rows and columns. I
have cells with the different dates (as text) across the top of my sheet
and
the different names (as text) down the side.

Any suggestions?

Thanks
Richard