Designating a workbook reference base on a cells contents

[PCLIVE]

I saw this in another post and I thought that if it was reworded, it might
make it easier to understand. In the formula below, at the point where it
says "Book2.xls", I was wondering if there would be a way to have "2"
designated by the contents of a cell in the current worksheet. For example,
A1 contains 2. It could be any number, but I want the file reference to
change based on the contens of A1. Is this possible?

='C:\temp\[Book2.xls]Sheet1'!$D$1

Thanks,
Paul

 

[Peo Sjoblom]

Yes and no, if the linked workbook is open (so no need for the path) you can
use INDIRECT

=INDIRECT("'[Book"&A1&"]Sheet1'!D1")

if not there are some workarounds, easiest is probably a UDF, Harlan Grove
has a function called PULL and Laurent Longre has an add-in called Morefunc
that has a function called INDIRECTT

ftp://members.aol.com/hrlngrv/

http://xcell05.free.fr/

 

[PCLIVE]

Thanks Peo,

As you said, it does work if the workbook is open. However, since the
Workbook reference is subject to change based on A1, then this won't be the
route that I'll take. I will look into the other suggestions you gave.

Thanks again,
Paul

Yes and no, if the linked workbook is open (so no need for the path) you
can
use INDIRECT

=INDIRECT("'[Book"&A1&"]Sheet1'!D1")

if not there are some workarounds, easiest is probably a UDF, Harlan Grove
has a function called PULL and Laurent Longre has an add-in called
Morefunc
that has a function called INDIRECTT

ftp://members.aol.com/hrlngrv/

http://xcell05.free.fr/



--

Regards,

Peo Sjoblom

I saw this in another post and I thought that if it was reworded, it
might
make it easier to understand. In the formula below, at the point where
it
says "Book2.xls", I was wondering if there would be a way to have "2"
designated by the contents of a cell in the current worksheet. For example,
A1 contains 2. It could be any number, but I want the file reference to
change based on the contens of A1. Is this possible?

='C:\temp\[Book2.xls]Sheet1'!$D$1

Thanks,
Paul