create a hyperlink and value link between two cells

[skippingrock]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello,
I have a workbook and in one sheet I have rows of description information and in one column I have created IDs for each of these rows. In my case these are channels of a portal that we are building with a name of the Channel, a description and other information.

In another sheet I have lists of tasks and I want to be able to reference the IDs of the Channels that I created in the other sheet so that we are able to know which Channels to which a task is related.

What we've been using is something like:
=CONCATENATE('Channel IDs & Content Owners'!A58, 'Channel IDs & Content Owners'!A33)

which in my case displays:
uS1-Ch-056 uS1-Ch-031
(we stuck a space in the front of each ID so that it spaced better)

I found out that I could also type:
='Channel IDs & Content Owners'!A58&'Channel IDs & Content Owners'!A33
to display the same thing (I don't know what is better, except that the later is easier to type.

What I really want to be able to do is do what I've done above, but also have the IDs be a link that when clicked on in it would take the user to the cell that was referenced.

So in my example I would see a cell with:
uS1-Ch-056 uS1-Ch-031

If I clicked on: uS1-Ch-056
I would be taken from that cell to cell located at 'Channel IDs & Content Owners'!A58.

Is this possible in Excel? It would sure make navigating our spreadsheet easier. Whenever we see an ID, we could click on the ID and be taken to the original cell to read the referring material.

thanks,
-skipR

 

[Laroche J]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello,
I have a workbook and in one sheet I have rows of description information and
in one column I have created IDs for each of these rows. In my case these are
channels of a portal that we are building with a name of the Channel, a
description and other information.

In another sheet I have lists of tasks and I want to be able to reference the
IDs of the Channels that I created in the other sheet so that we are able to
know which Channels to which a task is related.

What we've been using is something like:
=CONCATENATE('Channel IDs & Content Owners'!A58, 'Channel IDs & Content
Owners'!A33)

which in my case displays:
uS1-Ch-056 uS1-Ch-031
(we stuck a space in the front of each ID so that it spaced better)

I found out that I could also type:
='Channel IDs & Content Owners'!A58&'Channel IDs & Content Owners'!A33
to display the same thing (I don't know what is better, except that the later
is easier to type.

What I really want to be able to do is do what I've done above, but also have
the IDs be a link that when clicked on in it would take the user to the cell
that was referenced.

So in my example I would see a cell with:
uS1-Ch-056 uS1-Ch-031

If I clicked on: uS1-Ch-056
I would be taken from that cell to cell located at 'Channel IDs & Content
Owners'!A58.

Is this possible in Excel? It would sure make navigating our spreadsheet
easier. Whenever we see an ID, we could click on the ID and be taken to the
original cell to read the referring material.

thanks,
-skipR

It could be done with a macro. Unfortunately your version 2008 doesn't have
VBA support. For the others here's what it would look like:

In the VBA window of the tasks sheet, insert:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, _
Cancel As Boolean)

t = Target.Value
sp = InStr(2, t, " ", 1)

If sp > 0 Then
t1 = Mid(t, 1, sp - 1)
Else
t1 = ""
End If

Set code_line = Worksheets("Channel IDs & Content Owners"). _
Range("A:A").Find(what:=t1)

If Not code_line Is Nothing Then
Application.Goto Reference:= _
Worksheets("Channel IDs & Content Owners"). _
Range(code_line.Address)
Else
MsgBox "code not found"
End If

End Sub

When you double-click on the cell of the concatenated code you'll be taken
to the corresponding code in the channels sheet.

Regarding the concatenation: if I were you I would not put spaces in front
of all my codes. If all you want is spaces in the concatenated string,
insert the spaces in the concatenation formula:
=" " & 'Channel IDs & Content Owners'!A58 & " " & 'Channel IDs & Content
Owners'!A33
The biggest advantage is that nobody will ever forget to put the spaces in
the channel and tasks codes.

In Format / Cells / Alignment, you can also specify how much indent (white
space) you want at the beginning of a cell without actually adding a space
to the content of the cell.

JL
Mac OS X 10.4.11, Office v.X 10.1.9