Formula Link to external Workbooks Problem

[cmscriven]

Hi

My first post (I registered as this is driving me mad).

I want to insert a variable (i.e. cell data) within a formula that points at a cell in an external workbook. I am aware that you need to put the full path and apostrophes in the formula and it works fine if typed in fully such as ='c:\dir1\dir2\dir3\[file.xls]UserData'!A1. The A1 ref will vary depending on which cell i want the data from.
My problem is that I want to replace the path & file information with variable path & file info held in a suitable cell in the main workbook (I have all the relevant directory & path information available) but I can find no way of introducing the data into the formula without getting the dreaded REF#. I have tried using INDIRECT but this only works when the workbooks are open which is not practical for my usage. I have a feeling this is not possible but I thought I'd give the experts a go. It seems like such a simple thing to do!

Thanks

 

[Richard Schollar]

Hi Chris

You'd have to do it via a macro (you could record a macro of you changing the source of external links to show you the code you'd need).

Richard

 

[GlennUK]

It seems like such a simple thing to do!

... 'fraid not. But, you could use Laurent Longre's INDIRECT.EXT function, included in his free Morefunc add-in, found here:
http://xcell05.free.fr/english/

 

[cmscriven]

Thanks for the info. I shall follow both pieces of advice and let you know.