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.

wrote:
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")

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



wrote in message oups.com...
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.

Conan Kelly wrote:
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



wrote in message oups.com...
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).