Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How to setup a hyperlink to an indirect function?
I have a formula;
=HYPERLINK(INDIRECT($A225&"!"&"$B$4"),INDIRECT($A2 25&"!"&"$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. |
#2
|
|||
|
|||
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 <atyahoo<dotcom ---- "Skot" wrote in message ... I have a formula; =HYPERLINK(INDIRECT($A225&"!"&"$B$4"),INDIRECT($A2 25&"!"&"$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. |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- "Skot" wrote in message ... I have a formula; =HYPERLINK(INDIRECT($A225&"!"&"$B$4"),INDIRECT($A2 25&"!"&"$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. |
#4
|
|||
|
|||
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 <atyahoo<dotcom ---- "Skot" wrote in message ... 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 |
#5
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#6
|
|||
|
|||
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 <atyahoo<dotcom ---- "Skot" wrote in message ... 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 |
#7
|
|||
|
|||
check help for the CELL worksheet function
=CELL("address",A1) returns $A$1 i.e. the address of the reference cell. Mangesh "Skot" wrote in message ... 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 <atyahoo<dotcom ---- "Skot" wrote in message ... 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 |
#8
|
|||
|
|||
Glad to hear that !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Skot" wrote in message ... I don't quite understand what it all is... but it works brilliantly!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Using INDIRECT function to specify source data | Charts and Charting in Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Sum Indirect function through multiple sheets | Excel Discussion (Misc queries) | |||
hyperlink function | Excel Worksheet Functions |