Indirect?
 

I am trying to create a formula that allows me to update a link to another
workbook by changing the value within a cell embedded in the current
workbook. I was thinking that InDirect would allow me to do this, but I
cannot get it to function properly. As an example, I am trying to update the
worksheet reference that is pulling data into the worksheet:

\\Hyperion\Shared\2006\P2\"file name""worksheet""cell reference"

What I need to do is keep the workbook reference but change the "P2" as our
periods change, ie to P3, P4, etc, without have to do an edit replace. I was
hoping to place a cell in the worksheet that would represent the current
period, and when this cell is updated it would also update the "P" reference
in the link referenced above. This will also be necessary for the
"worksheet" reference in the example above. If anyone has thoughts on how to
accomplish this it would be appreciated. Thanks.

Indirect?
 

On Thu, 2 Mar 2006 08:14:40 -0800, moose0507
wrote:

I am trying to create a formula that allows me to update a link to another
workbook by changing the value within a cell embedded in the current
workbook. I was thinking that InDirect would allow me to do this, but I
cannot get it to function properly. As an example, I am trying to update the
worksheet reference that is pulling data into the worksheet:

\\Hyperion\Shared\2006\P2\"file name""worksheet""cell reference"

What I need to do is keep the workbook reference but change the "P2" as our
periods change, ie to P3, P4, etc, without have to do an edit replace. I was
hoping to place a cell in the worksheet that would represent the current
period, and when this cell is updated it would also update the "P" reference
in the link referenced above. This will also be necessary for the
"worksheet" reference in the example above. If anyone has thoughts on how to
accomplish this it would be appreciated. Thanks.

This is untested

With a cell named "period" which contains the Value [P2] and a cell
named WS which contains the value [worksheet], use the following -
watch the word wrap.

="\\Hyperion\Shared\2006\"&Period&"\"&""""&"fil e
name"&""""&""""&WS&""""&""""&"cell reference"&""""

HTH

Richard Buttrey
__