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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

yes always in column

--
Message posted from http://www.ExcelForum.com

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


---
Message posted from http://www.ExcelForum.com/

do we have a way other than this one??

like with excel formuls or something


thanks lot piku

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy

"pikus " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

vikram

=HYPERLINK(CELL("address",INDEX(Sheet2!$A$1:$A$100 0,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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"vikram " wrote in message
...
do we have a way other than this one??

like with excel formuls or something


thanks lot pikus


---
Message posted from http://www.ExcelForum.com/

I could not understand that macro!

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

See Dick's suggestion.

--
Regards,
Tom Ogilvy

"vikram " wrote in message
...
I could not understand that macro!

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


---
Message posted from http://www.ExcelForum.com/

i am not able to apply it

sorry for the ignoranc

--
Message posted from http://www.ExcelForum.com

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"vikram " wrote in message
...
Hi Kusleika,

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

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


thank u so much


---
Message posted from http://www.ExcelForum.com/

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.

--
Regards,
Tom Ogilvy

"pikus " wrote in message
...
That was pretty obfuscated huh? It stood for Sheet2Length. It made
sense while I was writing it. - Pikus


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

hey tom,mike and debra


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

--
Message posted from http://www.ExcelForum.com

vikram

=HYPERLINK(CELL("address",INDEX(Sheet2!$A$1:$A$100 0,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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"vikram " wrote in message
...
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 changing
sheet2 to sheet 1 in the formula

plese advise

thanks a lot


---
Message posted from http://www.ExcelForum.com/