Linking Sheets

[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

 

[pikus]

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

 

[vikram]

yes always in column

 

[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

 

[vikram]

do we have a way other than this one??

like with excel formuls or something


thanks lot piku

 

[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?

 

[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.

 

[vikram]

I could not understand that macro!

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

 

[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

 

[pikus]

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

 

[Tom Ogilvy]

See Dick's suggestion.

 

[vikram]

i am not able to apply it

sorry for the ignoranc

 

[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.

 

[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.

 

[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

 

[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

 

[vikram]

hey tom,mike and debra


do u have any idea for this one...not a macro but a formula would 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.