Tech Support banner

Status
Not open for further replies.
1 - 4 of 4 Posts

·
Registered
Joined
·
55 Posts
Discussion Starter · #1 ·
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
 

·
Superhuman Computer
Joined
·
1,632 Posts
Try using the formula INDIRECT() with the concatenated workbook/cell reference inside the brackets. This should work.
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
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
 

·
Registered
Joined
·
55 Posts
Discussion Starter · #4 · (Edited)
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
 
1 - 4 of 4 Posts
Status
Not open for further replies.
Top