Change links to hyperlinks in a workbook?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 5 Income Statement sheets in a workbook. The first sheet is an Income
Statement summing four departments (listed by columns). Column B has Dept 1
data, column C has Dept 2 data, etc. ALL data on this sheet is linked
(referenced) to the other four sheets which are the individual departments.

I know I can set hyperlinks one at a time, but is there a way for me to have
each of those links (every cell containing the data from the other four
sheets) become hyperlinks?

If I have to add another sheet that mirrors the summary sheet but has
hyperlinks, that would be fine. I am just showing the summary sheet to a
client and would like to be able to click on an amount that they have a
question about and have it take me to the input cell on the department's
sheet.

I mean, afterall, they are all links already - just not hyperlinks...

Thanks in advance!

-Steve
 
You know, if you turn off Edit Directly in Cells under Tools, Options, Enter
then all you have to do is double-click a formula cell and the source cell
will be selected. And you can press F5 and then Enter to go back.

--
Jim Rech
Excel MVP
|I have 5 Income Statement sheets in a workbook. The first sheet is an
Income
| Statement summing four departments (listed by columns). Column B has Dept
1
| data, column C has Dept 2 data, etc. ALL data on this sheet is linked
| (referenced) to the other four sheets which are the individual
departments.
|
| I know I can set hyperlinks one at a time, but is there a way for me to
have
| each of those links (every cell containing the data from the other four
| sheets) become hyperlinks?
|
| If I have to add another sheet that mirrors the summary sheet but has
| hyperlinks, that would be fine. I am just showing the summary sheet to a
| client and would like to be able to click on an amount that they have a
| question about and have it take me to the input cell on the department's
| sheet.
|
| I mean, afterall, they are all links already - just not hyperlinks...
|
| Thanks in advance!
|
| -Steve
 
Thanks Jim! But is there a way to have only one sheet work like that, all
the time without changing settings in the menu? So I can turn that feature
on for a specific sheet and leave it on, until I want to turn it off.

I would like to be able to email the file to a client and have them just be
able to click on the cell on the summary page to be taken to the cell on the
supporting department's sheet.

Thanks again for the reply...

-Steve
 
It looks like what you need are true hyperlinks since you're mailing this
off. You could try this macro I just threw together (disclaimor!). Select
a range of formulas and run it:

Sub MakeHyperLinks()
Dim Cell As Range
For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:="",
SubAddress:=Mid(Cell.Formula, 2)
Next
End Sub

This assumes each formula is a simple =Sheet1!A10. If it's not like that
this will not produce a useful hyperlink.

To remove hyperlinks from a selected range:

Sub RemoveHyperLinks()
Selection.Hyperlinks.Delete
End Sub

--
Jim Rech
Excel MVP
| Thanks Jim! But is there a way to have only one sheet work like that, all
| the time without changing settings in the menu? So I can turn that
feature
| on for a specific sheet and leave it on, until I want to turn it off.
|
| I would like to be able to email the file to a client and have them just
be
| able to click on the cell on the summary page to be taken to the cell on
the
| supporting department's sheet.
|
| Thanks again for the reply...
|
| -Steve
|
| "Jim Rech" wrote:
|
| > You know, if you turn off Edit Directly in Cells under Tools, Options,
Enter
| > then all you have to do is double-click a formula cell and the source
cell
| > will be selected. And you can press F5 and then Enter to go back.
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > |I have 5 Income Statement sheets in a workbook. The first sheet is an
| > Income
| > | Statement summing four departments (listed by columns). Column B has
Dept
| > 1
| > | data, column C has Dept 2 data, etc. ALL data on this sheet is linked
| > | (referenced) to the other four sheets which are the individual
| > departments.
| > |
| > | I know I can set hyperlinks one at a time, but is there a way for me
to
| > have
| > | each of those links (every cell containing the data from the other
four
| > | sheets) become hyperlinks?
| > |
| > | If I have to add another sheet that mirrors the summary sheet but has
| > | hyperlinks, that would be fine. I am just showing the summary sheet
to a
| > | client and would like to be able to click on an amount that they have
a
| > | question about and have it take me to the input cell on the
department's
| > | sheet.
| > |
| > | I mean, afterall, they are all links already - just not hyperlinks...
| > |
| > | Thanks in advance!
| > |
| > | -Steve
| >
| >
| >
 
Thanks Jim, but the macro gave me a syntax error. I know...disclaimer... ;)

But unfortunately I don't know VB so I am stuck, seemed like a great idea.
Maybe someone can help debug it???

Cheers,
Steve
 
There was a line wrap problem:

Sub MakeHyperLinks()
Dim Cell As Range
For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:="", _
SubAddress:=Mid(Cell.Formula, 2)
Next
End Sub


Note the addition of " _" on the hyperlinks.add line. This means the logical
line is continued on the next physical line.
 
Awesome - worked great!!!!

Thanks!!!!
-Steve

Dave Peterson said:
There was a line wrap problem:

Sub MakeHyperLinks()
Dim Cell As Range
For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:="", _
SubAddress:=Mid(Cell.Formula, 2)
Next
End Sub


Note the addition of " _" on the hyperlinks.add line. This means the logical
line is continued on the next physical line.
Click to expand...
 
Back
Top