How do I build a reference to an external worksheet?

[Alexsalles]

Hi,

I need to create a reference to an external worksheet (which is not
necessarily active) that could be used by other cells as in the
"indirect" function, but refering to external data.

My objective is to define the path of a certain file in a cell (like
C:\Documents and Settings\My Documents\Forecasts\File X), and define
different ranges of such spreadsheet in other cells. The reason for
that, is the need to use the exact same structure in several different
consolidating spreadsheets that I have to create. I would have
different consolidating spreadsheet, which would collect the same type
of information, in the same format, every month, but from different
sources. Every month, the name of the spreadhseets would change, so my
only work would be defining the new name in one cell and all my links
would all be automatically updated.

Does anybody now a relatively easy way to solve it?

Thanks,
Alex

 

[Biff]

Hi!

Does anybody now a relatively easy way to solve it?

The short answer is no. There is no EASY way to do this.

The use of Indirect is easy but using that has the huge disadvantage wherein
it requires that all the other linked files MUST be open for it to work.

There is a VBA procedure that does this but I'm not so sure getting it to
work is EASY! The name of the utility is PULL and the author is Harlan
Grove. If you do a search of these NG's you should be able to find it.

Biff

 

[Alexsalles]

OK Biff. Thanks anyway. Do you have any suggestion for better ways to
solve this problem? You can think of it as a recurrent process (run
once a month) involving the same information that comes from different
countries for example and therefore have to be consolidated per
country. Do you think the best way would be to rebuilt the link
manually for each country?
Cheers,
Alex

 

[Biff]

Do you think the best way would be to rebuilt the link

manually for each country?

You probably have to "bite the bullet" and manually enter the links. This is
when you learn the value of using short paths and short file names/sheet
names!

Biff