Making path of linked cell dynamic

[Negentropy]

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

[Bernard Liengme]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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]

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

[Negentropy]

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