Making path of linked cell dynamic
Hi all, Is there a syntax to make the path of a linked cell dynamic? I’ll explain with an example: Cell A1 is linked to another excel file, example.xls, in the folder week 40. So the formula in cell A1: ='C:\week _40_\[example.xls]Blad1'!$A$1 In this formula, I want the value _40_ to be dynamic, and read this value from cell B1. So lets say I put _41_ in to cell B1, the formula in cell A1 should read ='C:\week _41_\[example.xls]Blad1'!$A$1 Any way to go about this? Thanks a lot in advance! Cheers, Chris -- Negentropy ------------------------------------------------------------------------ Negentropy's Profile: http://www.excelforum.com/member.php...o&userid=27525 View this thread: http://www.excelforum.com/showthread...hreadid=470451 |
Try the INDIRECT function
=INDIRECT("'C:\week "&B1&"\[example.xls]Blad1'!$A$1") (untested - no time today) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Negentropy" wrote in message ... Hi all, Is there a syntax to make the path of a linked cell dynamic? I’ll explain with an example: Cell A1 is linked to another excel file, example.xls, in the folder week 40. So the formula in cell A1: ='C:\week _40_\[example.xls]Blad1'!$A$1 In this formula, I want the value _40_ to be dynamic, and read this value from cell B1. So lets say I put _41_ in to cell B1, the formula in cell A1 should read ='C:\week _41_\[example.xls]Blad1'!$A$1 Any way to go about this? Thanks a lot in advance! Cheers, Chris -- Negentropy ------------------------------------------------------------------------ Negentropy's Profile: http://www.excelforum.com/member.php...o&userid=27525 View this thread: http://www.excelforum.com/showthread...hreadid=470451 |
If you put the workbook name into another cell, you could use =indirect() to
build the reference to the other workbook. The bad news is that =indirect() will return an error if that other workbook isn't open. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Negentropy wrote: Hi all, Is there a syntax to make the path of a linked cell dynamic? I’ll explain with an example: Cell A1 is linked to another excel file, example.xls, in the folder week 40. So the formula in cell A1: ='C:\week _40_\[example.xls]Blad1'!$A$1 In this formula, I want the value _40_ to be dynamic, and read this value from cell B1. So lets say I put _41_ in to cell B1, the formula in cell A1 should read ='C:\week _41_\[example.xls]Blad1'!$A$1 Any way to go about this? Thanks a lot in advance! Cheers, Chris -- Negentropy ------------------------------------------------------------------------ Negentropy's Profile: http://www.excelforum.com/member.php...o&userid=27525 View this thread: http://www.excelforum.com/showthread...hreadid=470451 -- Dave Peterson |
Hi, Thanks for pointing me to the indirect() function, it's been very helpful! Pity that it only works when the other file is open as mentioned, some sheets I want to use it in link to 10+ files. I'll have a closer look at that pull file (Thanks!) but seeing im a n00b when it comes to vb I'll do this with a bit clearer head tomorrow ;) Thanks for the help! Cheers, Chris -- Negentropy ------------------------------------------------------------------------ Negentropy's Profile: http://www.excelforum.com/member.php...o&userid=27525 View this thread: http://www.excelforum.com/showthread...hreadid=470451 |
All times are GMT +1. The time now is 06:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com