Use a cell to name a file to be opened.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 30 Jun 2008 21:00 GMT

I want to use the contents of a cell as part of an INDEX lookup. The
contents of the cell would indicate the name of the file I want to open. Any
ideas?

Reply to this Message

Jarek Kujawa - 30 Jun 2008 21:44 GMT

INDEX works on open files only

you might try to combine INDEX with INDIRECT

Reply to this Message

Jarek Kujawa - 30 Jun 2008 21:55 GMT

e.g.

=INDIRECT('C:\Documents and Settings\[&A1&".xls]Bonds'!$H$6")

where A1=Somefile

will retrieve data from file "Somefile.xls" stored in C:\Documents and
Settings\, from cell H6 in worksheet named "Bonds"

Reply to this Message

Dave Peterson - 30 Jun 2008 22:36 GMT

It's not =index() that has trouble with closed files.

=indirect() has trouble with closed files.

> e.g.
>
[quoted text clipped - 4 lines]
> will retrieve data from file "Somefile.xls" stored in C:\Documents and
> Settings\, from cell H6 in worksheet named "Bonds"

Signature

Dave Peterson

Reply to this Message