Lookup value in a list and find it in another worksheet

[SwiftM]

Hi all,

In Range B3:B12 I have the numbers 1,2,3,4,5,6,7,8 etc. This is in worksheet "Log".

In Range B1:K1 I have the same numbers, but this is in worksheet "Admin".

What I would like to do, using the DoubleClick option is when I double click on a cell with one of those numbers in in "Log" worksheet, vba looks up that cell value on worksheet "Admin" range B1:K1 and goes to that matching cell.

I have absolutely no idea how to do this.

Could anyone please help me?

Many thanks!

Kind regards,

 

[DanteAmor]

You can put the following formula, for example in column C3 to C12.
Click on any of the cells in column C and the cursor will go to that cell.
Change the "Go to" formula to the text you want.

Dante Amor
	A	B	C
1
2
3		1	Go to: 1
4		3	Go to: 3
5		5	Go to: 5
6		7	Go to: 7
7		9	Go to: 9
8		11	Go to: 11
9		13	Go to: 13
10		15	Go to: 15
11		17	Go to: 17
12		19	Go to: 19
Log

Cell Formulas
Range	Formula
C3:C12	C3	=HYPERLINK("#"&CELL("address",INDEX(Admin!$B$1:$K$1,MATCH(B3,Admin!$B$1:$K$1,0))),"Go to: "&B3)

 

[SwiftM]

You can put the following formula, for example in column C3 to C12.
Click on any of the cells in column C and the cursor will go to that cell.
Change the "Go to" formula to the text you want.

Dante Amor
	A	B	C
1
2
3		1	Go to: 1
4		3	Go to: 3
5		5	Go to: 5
6		7	Go to: 7
7		9	Go to: 9
8		11	Go to: 11
9		13	Go to: 13
10		15	Go to: 15
11		17	Go to: 17
12		19	Go to: 19
Log

Cell Formulas
Range	Formula
C3:C12	C3	=HYPERLINK("#"&CELL("direccion",INDEX(Admin!$B$1:$K$1,MATCH(B3,Admin!$B$1:$K$1,0))),"Go to: "&B3)

Thank you, Dante, but it comes up as a #VALUE! error in each cell.

So, when I click on cell C3 where the formula is placed, it should lookup the value in B3 in "Log", match it with one of the cells in range B1:K1 in "Admin" and then goto that cell in "Admin"?

 

[DanteAmor]

I think my correction was late.
Copy again the formula from post 2

=HYPERLINK("#"&CELL("address",INDEX(Admin!$B$1:$K$1,MATCH(B3,Admin!$B$1:$K$1,0))),"Go to: "&B3)

 

[SwiftM]

I think my correction was late.
Copy again the formula from post 2

=HYPERLINK("#"&CELL("address",INDEX(Admin!$B$1:$K$1,MATCH(B3,Admin!$B$1:$K$1,0))),"Go to: "&B3)

Absolutely brilliant, thank you Dante.

Just another real quick question if I could pick your brains....

When there is no value in a cell in the range B3:B12, it will obviously show a #N/A value. Is there a way in which I can hide this error, and when a value is inserted then formula then kicks in and shows the "Go To" formula?

Thanks again.

 

[DanteAmor]

Is there a way in which I can hide this error

=IFERROR(HYPERLINK("#"&CELL("address",INDEX(Admin!$B$1:$K$1,MATCH(B3,Admin!$B$1:$K$1,0))),"Go to: "&B3),"")

 

[Flaiban]

If you want the doubleclick event...
Double click on the Log Sheet and insert this code :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, [B:B]) Is Nothing Then
        Call find
    End If
End Sub

After , insert a module with this code below :

Sub find()
rngB = ActiveCell.Value

    Sheets("Admin").Select
    Columns("B:K").Select
    Selection.find(What:=rngB, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
      
ActiveCell.Offset(0, 0).Select
End Sub