Insert cell value into a link path

[krisue]

This may not be the most efficient way to do this, but here's what I'm
trying to do. I have the following link in B3:
='D:\data\Upgrades\Reports\060801\[Down.xls]Upgrade Time Per
Agency2'!$C$3

What I have in B4 is the =TODAY() formula and I have it formatted as
060801 (August 1st, 2006). What I want to do is be able to have the
link in B3 contain the value in B4 so that it's dynamic. I can change
B4 to whatever and it would give me the new data. It would be
something like (but this doesn't work obviously):

='D:\data\Upgrades\Reports\$B$4\[Down.xls]Upgrade Time Per
Agency2'!$C$3

How can I put in the cell reference here? I haven't been able to find
anything. I've tried putting it in single quotes, double quotes, and
just by itself.

Thanks.

 

[krisue]

This may not be the most efficient way to do this, but here's what I'm
trying to do. I have the following link in B3:
='D:\data\Upgrades\Reports\060801\[Down.xls]Upgrade Time Per
Agency2'!$C$3

What I have in B4 is the =TODAY() formula and I have it formatted as
060801 (August 1st, 2006). What I want to do is be able to have the
link in B3 contain the value in B4 so that it's dynamic. I can change
B4 to whatever and it would give me the new data. It would be
something like (but this doesn't work obviously):

='D:\data\Upgrades\Reports\$B$4\[Down.xls]Upgrade Time Per
Agency2'!$C$3

How can I put in the cell reference here? I haven't been able to find
anything. I've tried putting it in single quotes, double quotes, and
just by itself.

Thanks.

Found it. Guess I didn't look hard enough:
=INDIRECT("'D:\data\Upgrades\Reports\" & B4 & "\[Down.xls]Upgrade Time
Per Agency2'!$C$3")

 

[Conan Kelly]

krisue,

You will have to use the INDIRECT function it will be something like this:

=INDIRECT("='D:\data\Upgrades\Reports\" & $B$4 & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")

You may also have to wrap the B4 cell reference in the TEXT function (or something similar) like this:

=INDIRECT("='D:\data\Upgrades\Reports\" & TEXT($B$4) & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")

I don't recall if by using the INDIRECT function if you will need the "=" sign inside the quotes, so you might try these as well:

=INDIRECT("'D:\data\Upgrades\Reports\" & $B$4 & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")
=INDIRECT("'D:\data\Upgrades\Reports\" & TEXT($B$4) & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")

I hope this helps,

Conan Kelly

 

[krisue]

krisue,

You will have to use the INDIRECT function it will be something like this:

=INDIRECT("='D:\data\Upgrades\Reports\" & $B$4 & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")

You may also have to wrap the B4 cell reference in the TEXT function (or something similar) like this:

=INDIRECT("='D:\data\Upgrades\Reports\" & TEXT($B$4) & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")

I don't recall if by using the INDIRECT function if you will need the "=" sign inside the quotes, so you might try these as well:

=INDIRECT("'D:\data\Upgrades\Reports\" & $B$4 & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")
=INDIRECT("'D:\data\Upgrades\Reports\" & TEXT($B$4) & "\[Down.xls]Upgrade Time Per Agency2'!$C$3")

I hope this helps,

Conan Kelly

This may not be the most efficient way to do this, but here's what I'm
trying to do. I have the following link in B3:
='D:\data\Upgrades\Reports\060801\[Down.xls]Upgrade Time Per
Agency2'!$C$3

What I have in B4 is the =TODAY() formula and I have it formatted as
060801 (August 1st, 2006). What I want to do is be able to have the
link in B3 contain the value in B4 so that it's dynamic. I can change
B4 to whatever and it would give me the new data. It would be
something like (but this doesn't work obviously):

='D:\data\Upgrades\Reports\$B$4\[Down.xls]Upgrade Time Per
Agency2'!$C$3

How can I put in the cell reference here? I haven't been able to find
anything. I've tried putting it in single quotes, double quotes, and
just by itself.

Thanks.

Thanks Conan. I found what I was looking for. But the TEXT option
will work well. I was going to reference another cell that had
TEXT(B4).