Excel - Using a variable to link to an external workbook

sharkei · 10-13-2005, 05:27 AM

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** · 10-13-2005, 09:12 AM

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

**Glaswegian** · 10-13-2005, 02:36 PM

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...func_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 · 10-17-2005, 07:36 AM

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

10-13-2005, 05:27 AM	#1 (permalink)
sharkei Registered User Join Date: Sep 2005 Posts: 55 OS: Windows XP Home	Excel - Using a variable to link to an external workbook 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

10-13-2005, 09:12 AM	#2 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,606 OS: WinXP Pro SP2 My System	Try using the formula INDIRECT() with the concatenated workbook/cell reference inside the brackets. This should work. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

10-13-2005, 02:36 PM	#3 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 28,100 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	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...func_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 __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

10-17-2005, 07:36 AM	#4 (permalink)
sharkei Registered User Join Date: Sep 2005 Posts: 55 OS: Windows XP Home	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 Last edited by sharkei; 10-17-2005 at 07:38 AM.