[Solved]How do I use Hyperlinks to link cells in different s

bobbysknob · Newbie Joined: 05 May 2011 Posts: 3

i have a spreadsheet for a 90 bottle wine rack, sheet 2 contains detailed info for each bottle Domaine, Country, Year etc. sheet 1 is a 9x10 grid representing the wine rack. What I am struggling with is getting a hyperlink to link a cell on sheet 1 to a cell on sheet 2 using the text in sheet 2 (ie Domaine) as the link in sheet 1 ( its location on the rack). I can make a hyperlink or have the text visible but not both.
many thanks in advance

ken johnson · Posted: Thu May 05, 2011 5:14 pm Post subject:

I'm not sure about the direction you are wanting the link to go; Rack Grid to Table or vice versa, both are possible.
To demonstrate one way, the attached doc (HyperDrinks.ods) has two rack grid positions (D6 and F4) that you will be taken to depending on which sheet2 table row hyperlink is clicked.
I've used the HYPERLINK function along with the CELL and SUBSTITUTE functions to construct the links' destination within the sheet1 Rack Grid. The sheet1 Rack Grid cell, to which the clicked hyperlink takes you, is extracted from an extra column on sheet2 (Rack Cell Address).
Each hyperlink's displayed text (2nd parameter) is extracted from the table of Domaine, Country, Year, Etc.
When done this way the Hyperlinks are very easily set up as the formula is filled across columns and down rows.
The original Domaine, Country, Year, Etc table could be hidden.
http://www.mediafire.com/view/?tj2htciwwmy3pfg
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

bobbysknob · Newbie Joined: 05 May 2011 Posts: 3

Ken,
Thanks for your response, how ever it doesn't quite solve the problem I'm trying to solve,
What I would like to achieve is to be able to enter info into sheet2 A1 and have that also appear in sheet1 B2 and have that become a hyperlink back to sheet2 A1.
The reasoning behind this is that sheet2 holds all the detailed info over a number of columns and sheet1 is an graphical image of the wine rack and I only need Domaine name in sheet1. As the wine in the rack is consumed/replaced I update sheet2 and sheet1 is automatically updated.
This worked fine in Excel but moving away from Windoze to Linux has meant the change to OO.
Regards, Andy.

ken johnson · Posted: Sat May 07, 2011 1:49 pm Post subject:

Like this then?...
http://www.mediafire.com/view/?dyktena23frozos
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

bobbysknob · Newbie Joined: 05 May 2011 Posts: 3

Ken,
Thank you for your help, that's exactly what I was after.
Andy