Hyperlink Function

[Hershey]

I'm trying to create a hyperlink in a cell on Sheet1 to cell A1 in Sheet2.
The value of Sheet1!A3 is Sheet2.
I'm entering this formula, and no error value is returned, but it just
doesn't go there.
Here is the formula:

=HYPERLINK(INDIRECT(MID(CELL("filename"),FIND("[",CELL("filename")),FIND("]",CELL("filename"))-FIND("[",CELL("filename"))+1)&A3&"!$a$1"),A3)

Any ideas?

 

[Max]

In Sheet1,
I got this rendition to work ok:
=HYPERLINK("#"&CELL("address",INDIRECT("'"&A3&"'!A1")),A3)
where in A3: Sheet2
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik

 

[Hershey]

Thanks, Max
I learned to make use of 'address' info type
I learned to enclose sheet names within apostrophes
I noticed that the number sign has an effect on formatting, but I don't
understand why
The hyperlinked text is not underlined or blue, and is an isle of Veranda in
a sea fo Arial. How?

In Sheet1,
I got this rendition to work ok:
=HYPERLINK("#"&CELL("address",INDIRECT("'"&A3&"'!A1")),A3)
where in A3: Sheet2
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---

I'm trying to create a hyperlink in a cell on Sheet1 to cell A1 in Sheet2.
The value of Sheet1!A3 is Sheet2.
I'm entering this formula, and no error value is returned, but it just
doesn't go there.
Here is the formula:

=HYPERLINK(INDIRECT(MID(CELL("filename"),FIND("[",CELL("filename")),FIND("]",CELL("filename"))-FIND("[",CELL("filename"))+1)&A3&"!$a$1"),A3)

Any ideas?

 

[Max]

The hyperlinked text is not underlined or blue, and is an isle of Veranda

in
a sea fo Arial.

I'm not sure what's that, but think you can just format the formula cell in
the normal way to taste. The "#" in the formula has nothing to do with
formatting.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---

 

[Hershey]

So what function does it serve? Why have you inserted the # ?

 

[Max]

So what function does it serve? Why have you inserted the # ?

See David McRitchie's page:
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

" .. you can use the pound sign (#). By including the "#" the pathname can
be left off, these work: in Excel 2000, and as I understand from Excel 97
and up .. "
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---

 

[Dave Peterson]

That # is the subaddress indicator for the link.

This link:
http://contextures.com/xlFunctions02.html
will take you to the top of one of Debra Dalgleish's pages.

This link:
http://contextures.com/xlFunctions02.html#Trouble
will take you to the section(?) named Trouble on that page.

You may want to change the hyperlink style so that it's the way you want.

In xl2003:
format|Style
Use the dropdown to choose Hyperlink
Click Modify
and make it what you want.

Styles live in workbooks. So this change won't have any effect on any other
workbook.

Thanks, Max
I learned to make use of 'address' info type
I learned to enclose sheet names within apostrophes
I noticed that the number sign has an effect on formatting, but I don't
understand why
The hyperlinked text is not underlined or blue, and is an isle of Veranda in
a sea fo Arial. How?

In Sheet1,
I got this rendition to work ok:
=HYPERLINK("#"&CELL("address",INDIRECT("'"&A3&"'!A1")),A3)
where in A3: Sheet2
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---

I'm trying to create a hyperlink in a cell on Sheet1 to cell A1 in Sheet2.
The value of Sheet1!A3 is Sheet2.
I'm entering this formula, and no error value is returned, but it just
doesn't go there.
Here is the formula:

=HYPERLINK(INDIRECT(MID(CELL("filename"),FIND("[",CELL("filename")),FIND("]",CELL("filename"))-FIND("[",CELL("filename"))+1)&A3&"!$a$1"),A3)

Any ideas?

 

[Max]

Dave, thanks for chipping in with your explanations

Btw, would you happen to know what the OP meant by:

.. an isle of Veranda in a sea fo Arial.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,000, Files: 354, Subscribers: 53
xdemechanik
---

 

[Gord Dibben]

Max

I didn't see the original post but maybe

"An Isle of Verdana in a Sea of Arial"

Referring to Font Types


Gord

 

[Dave Peterson]

I think that's it, too.

An island of calm in a sea of hurricanes!

(Ok, that ain't good.)

 

[Max]

Aha, thanks, Gord & Dave
It does sound like poetic font

Max