Linking Sheets

V

vikram

I have a excel workbook in which there are 2 sheets
there are numbers in sheet 1 column A

what i want is that if i click the number in column A like A2, it find
that number in sheet 2 and take u there

something like a link

thank
 
P

pikus

Where in sheet 2 might the number be found? Always in column A? O
anywhere? Is there a pattern? - Piku
 
P

pikus

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Value <> "" Then
x = 0
s2len = Worksheets(2).UsedRange.Row _
+ Worksheets(2).UsedRange.Rows.Count
Do
x = x + 1
Loop Until Worksheets(2).Cells(x, 1).Value = Target.Value
Application.Goto Worksheets(2).Cells(x, 1), 1
End If
End Sub - Pikus
 
V

vikram

do we have a way other than this one??

like with excel formuls or something


thanks lot piku
 
T

Tom Ogilvy

What was s2Len supposed to be for - to keep it from looping until it runs
off the bottom if no match is found?
 
D

Dick Kusleika

vikram

=HYPERLINK(CELL("address",INDEX(Sheet2!$A$1:$A$1000,MATCH(1,Sheet2!$A$1:$A$1
000,1))),1)

The 1's in MATCH(1, ....)),1)

is the number in the cell. The first one finds the row in sheet2 that
matches and the second one is the "friendly name" of the hyperlink.
 
V

vikram

I could not understand that macro!

Tom do u hv any idea? how to do what i want to d
 
V

vikram

Hi Kusleika,

the sheet where i want to find cell is "Sheet 1" and from the sheet
want to find is "Commitment Balance Report"

can u plz tell the formula now and where shall i enter this formula


thank u so muc
 
P

pikus

That was pretty obfuscated huh? It stood for Sheet2Length. It mad
sense while I was writing it. - Piku
 
D

Dick Kusleika

vikram

You put the formula in whichever cell you want people to click. It sounds
like you want it in Column A, so put in A1. If the sheet you want to jump
TO is Sheet 1, then change Sheet2 to Sheet 1 everywhere in the formula.
 
T

Tom Ogilvy

It wasn't that it was obfuscated, but you want to the trouble to compute it
and then never used it - so I think you left some of the logic of your code
out. As written, you code offers no protection for not finding a match.
But it is a moot point anyway as the OP doesn't seem to know how to use
code.
 
P

pikus

Thanks. You're right. I usr For Next loops more often than not an
forgot to add the "Or x = s2Len"
Thanks again. - Piku
 
V

vikram

hI Kusleika,


what is this address in the formula?
thank u so much

cos When i put this formula in the cell I get NA# , even after changin
sheet2 to sheet 1 in the formula

plese advise

thanks a lo
 
V

vikram

hey tom,mike and debra


do u have any idea for this one...not a macro but a formula would d
 
D

Dick Kusleika

vikram

=HYPERLINK(CELL("address",INDEX(Sheet2!$A$1:$A$1000,MATCH(1,Sheet2!$A$1:$A$1
000,1))),1)

This formula breaks down like this

=HYPERLINK("hlink address", "friendly name")

The hlink address uses the CELL function to get the address of a particular
formula. If the number one is in A3 on Sheet2, then

MATCH(1,Sheet2!$A$1:$A$1000,1) will equal 3 and

INDEX(Sheet2!A1:A1000,3) will be the cell A3.

CELL("address", A3) will equal '[BookName]Sheet2'!$A$3

HYPERLINK('[BookName]Sheet2'!$A$3,1)

will create a hyperlink that shows one, but jumps to A3 when clicked.

If you have a space in your sheet name, then you need single quotes around
it. The MATCH function would look like this

MATCH(1,'Sheet 1'!$A$1:$A$1000,1)

If you're getting N/A, that means that no match was found - so the number 1
doesn't exist in Sheet 1!A1:A1000.
 
Top