How to setup a hyperlink to an indirect function?

I have a formula;

=HYPERLINK(INDIRECT($A225&"!"&"$B$4"),INDIRECT($A225&"!"&"$B$4"))

that is not working. It comes up with an error message "Cannot open
specified file".

Originally, the cell contained the following formula, but i'm trying to put
a hyperlink over the top of it.

=INDIRECT($A224&"!"&"$B$4")

This indirect statement basically returns the data in workbook / cell
'5'!B4. I want to be able to hyperlink to that.

Adapting from a David McRitchie's post,
and assuming cell A225 contains: 5
think you could try:

=HYPERLINK("#'5'!B4",INDIRECT("'"&$A225&"'!B4"))

where INDIRECT("'"&$A225&"'!B4") returns the data in '5'!B4
which will display in the formula cell as the hyperlinked text.

You may wish to check out more info at David's:
http://www.mvps.org/dmcritchie/excel....htm#hyperlink
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Skot" <Skot@discussions.microsoft.com> wrote in message
news:F7C20C5D-055A-4BB7-B3E8-89964E70EF64@microsoft.com...
> I have a formula;
>
> =HYPERLINK(INDIRECT($A225&"!"&"$B$4"),INDIRECT($A225&"!"&"$B$4"))
>
> that is not working. It comes up with an error message "Cannot open
> specified file".
>
> Originally, the cell contained the following formula, but i'm trying to
put
> a hyperlink over the top of it.
>
> =INDIRECT($A224&"!"&"$B$4")
>
> This indirect statement basically returns the data in workbook / cell
> '5'!B4. I want to be able to hyperlink to that.

Yep. That would work except for the other point i forgot to mention. The
reason why I am using the indirect is that the worksheet i am using changes
(i have over 250 lines for it).

For example.
In the cell A225 is the figure 10. That figure also corrosponds to the
worksheet called ten. Therefor, by clicking on my hyperlink (which is the
address), it will reference cell A225 (which is the job number), and which
will bring up that worksheet (10) which will show me the details of that job.

I hope that makes sense.

Basically, the cell that i am indirectly referencing must be able to be used
in a fill.

Appreciate your help so far.

Skot.

"Max" wrote:

> Adapting from a David McRitchie's post,
> and assuming cell A225 contains: 5
> think you could try:
>
> =HYPERLINK("#'5'!B4",INDIRECT("'"&$A225&"'!B4"))
>
> where INDIRECT("'"&$A225&"'!B4") returns the data in '5'!B4
> which will display in the formula cell as the hyperlinked text.
>
> You may wish to check out more info at David's:
> http://www.mvps.org/dmcritchie/excel....htm#hyperlink
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Skot" <Skot@discussions.microsoft.com> wrote in message
> news:F7C20C5D-055A-4BB7-B3E8-89964E70EF64@microsoft.com...
> > I have a formula;
> >
> > =HYPERLINK(INDIRECT($A225&"!"&"$B$4"),INDIRECT($A225&"!"&"$B$4"))
> >
> > that is not working. It comes up with an error message "Cannot open
> > specified file".
> >
> > Originally, the cell contained the following formula, but i'm trying to
> put
> > a hyperlink over the top of it.
> >
> > =INDIRECT($A224&"!"&"$B$4")
> >
> > This indirect statement basically returns the data in workbook / cell
> > '5'!B4. I want to be able to hyperlink to that.
>
>
>

Try this other version (seems to work):

=HYPERLINK("#"&CELL("address",INDIRECT("'"&$A225&"'!B4")),INDIRECT("'"&$A225
&"'!B4"))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Skot" <Skot@discussions.microsoft.com> wrote in message
news:A0EB6B1A-8FDC-4B51-9191-8CC083A88209@microsoft.com...
> Yep. That would work except for the other point i forgot to mention. The
> reason why I am using the indirect is that the worksheet i am using
changes
> (i have over 250 lines for it).
>
> For example.
> In the cell A225 is the figure 10. That figure also corrosponds to the
> worksheet called ten. Therefor, by clicking on my hyperlink (which is the
> address), it will reference cell A225 (which is the job number), and which
> will bring up that worksheet (10) which will show me the details of that
job.
>
> I hope that makes sense.
>
> Basically, the cell that i am indirectly referencing must be able to be
used
> in a fill.
>
> Appreciate your help so far.
>
> Skot

And the formula is fillable down/across

Adapt the mixed cell reference in the INDIRECT part: $A225
to suit the intended fill
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

I don't quite understand what it all is... but it works brilliantly!!!

What is the "address" part of it?

"Max" wrote:

> Try this other version (seems to work):
>
> =HYPERLINK("#"&CELL("address",INDIRECT("'"&$A225&"'!B4")),INDIRECT("'"&$A225
> &"'!B4"))
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Skot" <Skot@discussions.microsoft.com> wrote in message
> news:A0EB6B1A-8FDC-4B51-9191-8CC083A88209@microsoft.com...
> > Yep. That would work except for the other point i forgot to mention. The
> > reason why I am using the indirect is that the worksheet i am using
> changes
> > (i have over 250 lines for it).
> >
> > For example.
> > In the cell A225 is the figure 10. That figure also corrosponds to the
> > worksheet called ten. Therefor, by clicking on my hyperlink (which is the
> > address), it will reference cell A225 (which is the job number), and which
> > will bring up that worksheet (10) which will show me the details of that
> job.
> >
> > I hope that makes sense.
> >
> > Basically, the cell that i am indirectly referencing must be able to be
> used
> > in a fill.
> >
> > Appreciate your help so far.
> >
> > Skot
>
>
>

check help for the CELL worksheet function
=CELL("address",A1) returns $A$1 i.e. the address of the reference cell.

Mangesh




"Skot" <Skot@discussions.microsoft.com> wrote in message
news:60123AF0-EEDA-4BBC-8655-0CE55832DB65@microsoft.com...
> I don't quite understand what it all is... but it works brilliantly!!!
>
> What is the "address" part of it?
>
> "Max" wrote:
>
> > Try this other version (seems to work):
> >
> >
=HYPERLINK("#"&CELL("address",INDIRECT("'"&$A225&"'!B4")),INDIRECT("'"&$A225
> > &"'!B4"))
> >
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > GMT+8, 1° 22' N 103° 45' E
> > xdemechanik <at>yahoo<dot>com
> > ----
> > "Skot" <Skot@discussions.microsoft.com> wrote in message
> > news:A0EB6B1A-8FDC-4B51-9191-8CC083A88209@microsoft.com...
> > > Yep. That would work except for the other point i forgot to mention.
The
> > > reason why I am using the indirect is that the worksheet i am using
> > changes
> > > (i have over 250 lines for it).
> > >
> > > For example.
> > > In the cell A225 is the figure 10. That figure also corrosponds to the
> > > worksheet called ten. Therefor, by clicking on my hyperlink (which is
the
> > > address), it will reference cell A225 (which is the job number), and
which
> > > will bring up that worksheet (10) which will show me the details of
that
> > job.
> > >
> > > I hope that makes sense.
> > >
> > > Basically, the cell that i am indirectly referencing must be able to
be
> > used
> > > in a fill.
> > >
> > > Appreciate your help so far.
> > >
> > > Skot
> >
> >
> >

Glad to hear that !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Skot" <Skot@discussions.microsoft.com> wrote in message
news:60123AF0-EEDA-4BBC-8655-0CE55832DB65@microsoft.com...
> I don't quite understand what it all is... but it works brilliantly!!!