|
Linking Sheets
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 |
Linking Sheets
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 |
Linking Sheets
|
Linking Sheets
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/ |
Linking Sheets
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 |
Linking Sheets
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/ |
Linking Sheets
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/ |
Linking Sheets
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 |
Linking Sheets
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 |
Linking Sheets
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 |
Linking Sheets
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/ |
Linking Sheets
|
Linking Sheets
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/ |
Linking Sheets
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/ |
Linking Sheets
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 |
Linking Sheets
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 |
Linking Sheets
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 |
Linking Sheets
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/ |
| All times are GMT +1. The time now is 03:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com