How to setup a hyperlink to an indirect function?

[Guest]

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.

 

[Max]

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/sheets.htm#hyperlink

 

[Guest]

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]

Try this other version (seems to work):

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

 

[Max]

And the formula is fillable down/across

Adapt the mixed cell reference in the INDIRECT part: $A225
to suit the intended fill

 

[Guest]

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

What is the "address" part of it?

 

[Mangesh Yadav]

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

Mangesh

 

[Max]

Glad to hear that !