Linking between one worksheet to another 1

[coolkake]

Hi, hoping someone can help me here

I have an excel document with two worksheets, one called Site and the other called Version. Both of these have a Location field in them. What I'm trying to do is allow the user to click the Location in the Site worksheet so it will take them to the Version worksheet and the Location that was clicked.

I have tried looking this up and cannot find what I'm looking for but this is probably due to the fact that I don't really know what I'm looking for

Cheers and Thanks in advance

 

[BJ9]

You'll need to set it up as a hyperlink. Within Excel (I'm using Excel 2003) go to insert, hyperlink. On the left hand side of the window it brings up, click on Place in this document and it will allow you to choose which worksheet and cell you want it to link to.

Hope this helps.

BJ

 

[coolkake]

Thanks, that gets me to the worksheet so now all I need to do is get it to go to the location that was clicked.
Example
If I click on 10 in Location on the worksheet Site I want it to go to 10 in Location on the Version worksheet.

Thanks for the very quick response, I'm further now in 10 minutes than I was after a couple hours

Cheers

 

[GlennUK]

Have a look here ...

http://www.mrexcel.com/board2/viewtopic.php?t=207355&highlight=

I'll be explaining the formula there tomorrow.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[coolkake]

Thanks but I'm lost.

I know how to add a hyperlink within my sheet (thanks BJ), but I do not know how to link to a specific cell within another sheet so I don't even know where to put this code.

I changed the Sheet2 to Version as that's my worksheet, was that part at least correct? When I try to use the hyperlink function whatever I had entered in my cell gets replaced with a 0 and is a link but it doesn't work.

Thoughts? And thanks

 

[GlennUK]

Which column is the location stored in Site? Which column in Version is location? What formula did you try? In which cell did you try it? What is the contents of any cell referenced by the formula?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[coolkake]

Column A is the Location field in Site
Column A is the Location field in Version
I tried this formula
=HYPERLINK("#"&SUBSTITUTE(CELL("address",Version!$D$9999),"9999",MATCH(A2,Version!D,0)),A2)
I highlighted all of Column A in Site and did an Hyperlink Insert and choose "Place in this Document" and choose the Version under Cell Reference and then pasted in the code where "type the cell reference" is.

An example of what the Location names look like is "STORE00030DVR01"

Thanks

 

[GlennUK]

Put that formula in cell B2 ( after altering Version!D to Version!A:A ), and copy down.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[coolkake]

You are the MAN!! This is Awesome, I'll have to play with it to see what does what but it worked like a charm for what I'm doing right now.

Thank you so much

Ken