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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> Alexsalles
> ------------------------------------------------------------------------
> Alexsalles's Profile:
> http://www.excelforum.com/member.php...o&userid=29643
> View this thread: http://www.excelforum.com/showthread...hreadid=493534
>

[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

"Alexsalles" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> Alexsalles
> ------------------------------------------------------------------------
> Alexsalles's Profile:
> http://www.excelforum.com/member.php...o&userid=29643
> View this thread: http://www.excelforum.com/showthread...hreadid=493534
>