Question on referencing Worksheets in other Workbooks

[Paul Laska]

I have a Workbook where I am trying to pull information
from other Workbooks, and I'm having trouble figuring out
the syntax.

What I have got to work so far in a cell reference is:

='Q:\Spreadsheets\[wkbk1.xls]Sheet1'!$A$1

What I need to do is be able to lookup the Sheet1 name in
a cell in another workbook. For example, something I've
tried but didn't work:

='Q:\Spreadsheets\[wkbk1.xls]'([ThisWorkbook]Sheet1!A1)!
$A$1

I appreciate any help anyone can provide. Thank you.

-Paul

 

[Dave Peterson]

I think you'd want to use the =indirect() function. But the bad news is that
=indirect() doesn't work with closed workbooks.

I have a Workbook where I am trying to pull information
from other Workbooks, and I'm having trouble figuring out
the syntax.

What I have got to work so far in a cell reference is:

='Q:\Spreadsheets\[wkbk1.xls]Sheet1'!$A$1

What I need to do is be able to lookup the Sheet1 name in
a cell in another workbook. For example, something I've
tried but didn't work:

='Q:\Spreadsheets\[wkbk1.xls]'([ThisWorkbook]Sheet1!A1)!
$A$1

I appreciate any help anyone can provide. Thank you.

-Paul

 

[Harlan Grove]

I think you'd want to use the =indirect() function. But the bad news is that
=indirect() doesn't work with closed workbooks.

...

True, but there are work-arounds.

http://www.google.com/[email protected]

 

[Paul Laska]

Dave and Harlan,

Thank you for taking the time to answer my Excel question
on the MSDN newsgroup. Sorry it's taken me so long to
respond, I've not gotten back to the forum till this
evening. I will give both your suggestions a try.

Dave, the good news is that in my case the other workbook
should always be open anyway, so your suggestion may work
just fine.

Harlan, the solution you suggested may be a good idea to
implement anyway, that way when someone forgets to open
the other workbook there won't be an issue. Plus, I've
never worked with the Indirect() function before (kinda
new to this level of use with Excel), so the site you
pointed me to is great for seeing examples of the syntax.

Thanks to both of you.

Sincerely,
Paul Laska