linking workbooks based on a variable factor

[thedovonron]

Hi,

Is it possible to have links to another file based on a variable factor
selected in the main sheet?

For example, the main file is currently linking to a JuneUK.xls file as I'm
displaying June data. If I were to change the main file to show July data (by
dropdown box and Indirect formula) is it then possible for the links to
realise that they need to be adjusted to link to a JulyUK.xls file?

Hope this is clear. Let me know if not.

Cheers!

 

[jasontferrell]

This is an example of what you would need. It references the cell in
row 2, column 3, of the Sheet1 sheet in workbook test.xls (you can
also use a path).

=INDIRECT(ADDRESS(2,3,1,TRUE,"[test.xls]Sheet1"))

 

[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.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

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