Hyperlink problem to cell in same workbook

[stainless]

I have a hyperlink created by a VB program that should link to a cell
on a different worksheet within the same book.

The hyperlink code currently is as follows:

=HYPERLINK(ADDRESS(4,2,1,FALSE,"MultipleAliases"),"MULTIPLE DP
ALIASES")

MultipleAliases is a separate worksheet and I want the link to jump to
row 4 cell 2 on this sheet.

I realise that normally you need a spreadsheet identifier as part of
the worksheet definition, thus the "MultipleAliases" would be
"[FILE1.XLS]MultipleAliases" if this was saved as FILE1.XLS.

If I do save this file with the altered hyperlink it works. The problem
is when the spreadsheet is first created, it is not yet saved (I guess
it is held in the temporary folder). Thus the hyperlink does not work,
even without [FILE1.XLS] in the text.

While this is still a temporary unsaved file, how do I define the sheet
identifier part of my ADDRESS statement so that it finds the
MultipleAliases sheet in the current document?

You can easily try this out by opening an excel spreadsheet, rename
Sheet2 to MultipleAliases and trying variations of the hyperlink text
in a Sheet1 cell.

Please note, I cannot define the cell to jump to in terms of a named
cell and can only use co-ordinates to identify it (due to this text
being created by VB), hence the use of ADDRESS to locate the cell.

Cheers

Mark

 

[Bernie Deitrick]

MArk,

Save the workbook in code, then add the hyperlink:

ActiveWorkbook.SaveAs Application.GetSaveAsFilename(, "Excel Files (*.xls), *.xls")
ActiveCell.Formula = _
"=HYPERLINK(""" & Worksheets("MultipleAliases"). _
Cells(4, 2).Address(False, False, xlA1, True) & _
""",""MULTIPLE DP ALIASES"")"

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

I'd use:

=HYPERLINK("#"&CELL("address",multiplealiases!B4),"multiple dp aliases")

I have a hyperlink created by a VB program that should link to a cell
on a different worksheet within the same book.

The hyperlink code currently is as follows:

=HYPERLINK(ADDRESS(4,2,1,FALSE,"MultipleAliases"),"MULTIPLE DP
ALIASES")

MultipleAliases is a separate worksheet and I want the link to jump to
row 4 cell 2 on this sheet.

I realise that normally you need a spreadsheet identifier as part of
the worksheet definition, thus the "MultipleAliases" would be
"[FILE1.XLS]MultipleAliases" if this was saved as FILE1.XLS.

If I do save this file with the altered hyperlink it works. The problem
is when the spreadsheet is first created, it is not yet saved (I guess
it is held in the temporary folder). Thus the hyperlink does not work,
even without [FILE1.XLS] in the text.

While this is still a temporary unsaved file, how do I define the sheet
identifier part of my ADDRESS statement so that it finds the
MultipleAliases sheet in the current document?

You can easily try this out by opening an excel spreadsheet, rename
Sheet2 to MultipleAliases and trying variations of the hyperlink text
in a Sheet1 cell.

Please note, I cannot define the cell to jump to in terms of a named
cell and can only use co-ordinates to identify it (due to this text
being created by VB), hence the use of ADDRESS to locate the cell.

Cheers

Mark