# Excel - Using a variable to link to an external workbook



## sharkei (Sep 1, 2005)

Hi to all Excel Gurus

I have a summary workbook which links data from a number of external workbooks. So an example of a formula in a cell is
='C:\My Documents\Department 1\April 05\[Data.xls]Sheet 1'!A1, where there could be any number of Departments. I currently roll forward to the next month by editing the links.

What I would like to be able to do, if possible, is to create dynamic links that are concatenated from variables contained in the summary workbook.

The formula would look like this:
=concatenate("'C:\My Documents\Department 1\",B1,"\[Data.xls]Sheet 1'!A1") where B1 contains the string "April 05". (Hope I've got the syntax right).

My attempts so far don't produce a link, Excel just evaluates the concatenation, and displays it as a string.

Maybe I am trying to exceeding basic Excel functionality and will have to use Visual Basic to achieve this.

Grateful for your thoughts.

Kind regards

Keith


----------



## ReeKorl (Mar 25, 2005)

Try using the formula INDIRECT() with the concatenated workbook/cell reference inside the brackets. This should work.


----------



## Glaswegian (Sep 16, 2005)

Hi Keith

In addition to Reekorl's suggestion, and if you want even more flexibility, try dowloading the Morefunc add-in. 

http://xcell05.free.fr/english/index.html#Morefunc_Functions

It includes the INDIRECT.EXT function which allows you to return the value of a cell in a closed workbook(plus lots of other useful functions).

Regards


----------



## sharkei (Sep 1, 2005)

Glaswegian and ReeKorl

Thanks for the link to the Add-in. I have seen a reference to it before, but didn't appreciate what extra functionality it provided. I needed to have the links work on closed files - there are probably too many to have open at the same time.

Kind regards

Keith


----------

