Insert cell value into a link path

  • Thread starter Thread starter krisue
  • Start date Start date
K

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.
 
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.
Click to expand...



Found it. Guess I didn't look hard enough:
=INDIRECT("'D:\data\Upgrades\Reports\" & B4 & "\[Down.xls]Upgrade Time
Per Agency2'!$C$3")
 
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
 
Conan said:
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.
Click to expand...
Click to expand...


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).
 
Back
Top