how can I display another cell as hyperlink text?

**scorpioscouse** · 10-16-2008, 11:13 AM

Hi, I'm new here and pretty new to excel. I have recently moved jobs and need to use excel alot more than previously and have found one or two stumbling blocks.
I have a workbook that has a main sheet which provides stats from other sheets within the workbook. I have a hyperlink which goes to a certain place of sheet 2 and the displayed text shows the contents of a cell on sheet 2.
I know it's possible to have the hyperlink go to a certain cell on another workbook as opposed to worksheet, but I cant display the contents of a cell on another workbook as the text on the hyperlink.
I'm sorry if this seems confusing, but I dont really know how else to explain it!
Thanks in advance for any help.

Scouse.

**mhuston** · 10-16-2008, 01:17 PM

If I understand you, you want workbook 1, sheet 1 to display the contents of workbook 2, sheet 1. If so then open both workbooks, select cell in workbook 2 and paste link into cell in workbook 1. It will look something like this: =[Book2.csv]Sheet1!$D$1

**sglife** · 10-17-2008, 02:03 AM

I think this is what you need

Code:

=HYPERLINK("[TEST.XLS]Sheet1!A1",Sheet1!A1)

**scorpioscouse** · 10-17-2008, 05:35 AM

kinda.. I'll try to explain a little better.
ok, I have value X in book1, sheet1, A1. I would like a hyper link on book2 to link to the cell on book 1. On the text to display for the hyperlink I would like it to show the contents of the cell on book1.
I have done it for different sheets, but not work books. The code I used was

Code:

=sheet2!a1

and I wrote this in the 'text to display' box so that on sheet 1 the hyper link displayed whatever was in cell A! on sheet 2 and acted as a hyperlink to that cell.

Thanks again.

Scouse.

**sglife** · 10-17-2008, 05:47 AM

Ok, I think I understand now
1. any cell, key in "=", then go to the target cell whose content you want to display
2. then press enter
3. close the target cell, you will see the full address of the workbook and worksheet and cell
4. copy the formula to "Text to display"

**Andy Pope** · 10-17-2008, 05:51 AM

This displays the contents of A1 on sheet1 in book3.xls

=HYPERLINK("#[Book3.xls]Sheet1!$A$1",[Book3.xls]Sheet1!$A$1)

**scorpioscouse** · 10-17-2008, 05:56 AM

The link works Andy, but the displayed text is "0" and not what is shown in the target cell.. I'm sorry to be an ***, but I can't figure it out!

**Andy Pope** · 10-17-2008, 06:03 AM

0 would suggest the cell being linked to is empty.

Let's start simple, can you get it to work when all the information is on the same sheet?

**scorpioscouse** · 10-17-2008, 06:08 AM

yea. I can link from one cell to another and display the contents of the target in the cell that is hyperlinked. I can also link to another sheet in the book and also display the contents of the target on sheet2 in the cell on sheet1. The problem comes when trying to link to another book all together. The link works, and it opens the target book and selects the target cell, but it wont echo the contents and display that as the text for the link. Do I make any sense? :D

**Andy Pope** · 10-17-2008, 06:14 AM

It makes sense but it is not what I get when testing.
What version are you using?

**scorpioscouse** · 10-17-2008, 06:22 AM

ermm pass! Probably an old one cause I work for the DWP :oD

It says Microsoft office XP enterprise edition?!?!?!?!?!

**Andy Pope** · 10-17-2008, 06:27 AM

I just tested in xl2000, version prior to yours, and it works.

Again the displaying of 0 would indicate the cell the 'Friendly_name' text is referencing is empty.

**scorpioscouse** · 10-17-2008, 06:32 AM

hmm. Ok, the code you wrote up there ^^^ should I put this in the function bar or do I make the cell a hyperlink and put the string into the 'TEXT TO DISPLY' box?!?

Sorry to be such a noob!

**Andy Pope** · 10-17-2008, 06:36 AM

That's a worksheet function so would go in a cell.

**scorpioscouse** · 10-17-2008, 06:50 AM

would it make a difference if the workbooks were on a shared network?
When I paste that function in it displays "1" when the value on sheet 3 is "5" and the function line is changed to

Code:

=HYPERLINK("#[Book3.xls]Sheet1!$A$1",'\\DFZ72594\Data\199711015\workgroup\RPCfat\CFAT\UNASSIGN\UNASSIGNED RECEIPTS MANAGEMENT\ARAT General Info\Name Search Rota\[Book3.xls]Sheet1'!$A$1)