dynamically link to other workbooks

[Remaniak]

Hi Guys,

Question:
I need to gather the values of the exact same cell but from 30 different workbooks.

Say the cell is 'Activities'!A1

The path of the workbook is 'D:\Documents and Settings\administrator\My Documents\[sales 2006.xls]Activities'!A1

But how do I create a formula that pickes up the Excel file name from a particular cell.

Thus:
='D:\Documents and Settings\administrator\My Documents\[B1]Activities'!A1

Thanks

Michiel

 

[DonkeyOte]

If the 30 workbooks are not open which I presume they are not you will need to use VBA to do this I'm afraid... unfortunately the *standard* INDIRECT formula will not work when linked to closed workbooks

 

[Jon von der Heyden]

INDIRECT.EXE can do it (I think). Search for this on the web - I think it's part of the morefunc addin. Can someone back this up?

 

[DonkeyOte]

I believe it does Jon but from what I've heard it can be slow to run and does not always work... I'd be inclined myself to build and return the formulas dynamically in VB routine as would you I guess.

 

[Remaniak]

Thanks guys,

In that case I'll just use the simple replace action on the formula's with Excel filenames.

Regards,

Michiel

 

[RoryA]

FWIW, it's INDIRECT.EXT but I've had some odd results with it.

 

[Jon von der Heyden]

FWIW, it's INDIRECT.EXT but I've had some odd results with it.

Ah yes, got .exe on the brain

Absolutely agree with you, I would favour code over this method any time!