Links to External Spreadsheet (using a variable in the link)

[Gar]

Hi,

We have a spreadsheet which is pulling in data from external spreadsheets.
So we use the following type of function to do so:

=([Budget.xls]Annual!C10:C25).

However, the external spreadsheets we use are updated everyday. So each day,
we have a new spreadheet named in a specific convention:

FilenameDDMMYY.xls

Is there a way to write the formula so that the DDMMYY part is dynamic, and
updates according to the current date? We've tried working around this, but
the way the function works, it doesn't seem to be able to take in a variable,
and must be static.

Thanks in advance!

 

[Gar]

Hi David,

Thanks for the response, i've looked into using the INDIRECT function in
Excel, but still haven't managed to get it working. I've tried a lot of
variations to the following formula's:

With & without extra ( ) brackets, and with and without

=('H:\[=indirect("A9")]TEST123'!A13)
=('H:\[=indirect(A9)]TEST123'!A13)
=('H:\[indirect(A9)]TEST123'!A13)
=('H:\[(indirect("A9"))]TEST123'!A13)

A9 being the name of the .XLS spreadsheet, Test123 being the sub-worksheet
and A13 the cell to read from.

Effectively, I want Excel to read the above formula as

=('H:\[file1.xls]Test123'!A13)

But file1.xs is obviously the variable I would like to have. It's possible
that this is not even technically possible, but I would like to explore the
possibility first, before giving up.

Any other ideas, guys??

Regards,

The function you need is INDIRECT.
--
David Biddulph

Hi,

We have a spreadsheet which is pulling in data from external spreadsheets.
So we use the following type of function to do so:

=([Budget.xls]Annual!C10:C25).

However, the external spreadsheets we use are updated everyday. So each
day,
we have a new spreadheet named in a specific convention:

FilenameDDMMYY.xls

Is there a way to write the formula so that the DDMMYY part is dynamic,
and
updates according to the current date? We've tried working around this,
but
the way the function works, it doesn't seem to be able to take in a
variable,
and must be static.

Thanks in advance!

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

========
Try something like:

=INDIRECT.ext("'c:\excel\[Filename"
& text(today(),"DDMMYY") & ".xls]Annual'!c1")
(all one line)

(Change the path to match what you need.)

But that's a guess. I don't use that addin.

But lots of people do. If you have trouble getting it to work, post back with
the drive/folder name and what you've tried. I'll bet you get help!

==========
An alternative may be to just copy today's workbook to a name that never
changes. Then the formula in excel won't have to worry about today's date.

Hi,

We have a spreadsheet which is pulling in data from external spreadsheets.
So we use the following type of function to do so:

=([Budget.xls]Annual!C10:C25).

However, the external spreadsheets we use are updated everyday. So each day,
we have a new spreadheet named in a specific convention:

FilenameDDMMYY.xls

Is there a way to write the formula so that the DDMMYY part is dynamic, and
updates according to the current date? We've tried working around this, but
the way the function works, it doesn't seem to be able to take in a variable,
and must be static.

Thanks in advance!