Is it possible? (click and directly jump to another sheet's cell)

[poli9]

Hi everyone!

I have 2 sheets (see below).
If I click on any number in my sheet1 first column, I'd like to be able to directly jump/go to the corresponding same number in my sheet2 (and vice-versa - toggle).

Example:
If I click on #3 in sheet1 (row4), then Excel directly goes to/position itself to #3 in sheet2 (row6).
Now sheet2 is active, if I click on #4 (row7), sheet1 becomes active and its cursor is directly positionned row6, in cell #4 in sheet1.

I have asked Google, read the documentation for hours but haven't solution yet, I'm afraid my Excel level is too low... I don't even know if it's possible !?! ... so any help is welcome.

Happy new year & Thanks!
sheet 1

1
2
3
4

<tbody>
</tbody>

sheet 2

1
2
3
4

<tbody>
</tbody>

 

[Ramballah]

So is it only for those 4 numbers? or you need it for more than those 4? And can you guarentee those numbers stay the same? Do you edit those numbers yourself or does something or someone update those numbers from a different file?

 

[poli9]

The numbers go up to 100000. I update the cells myself. Thanks!

 

[poli9]

up

 

[poli9]

up

 

[poli9]

hope!

 

[Eric W]

You can't do this with formulas, but you can get that effect using VBA. To try, open a copy of your workbook. On the Sheet1 tab on the bottom, right click and select View Code. Paste the following code in the window that opens:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Set nc = Sheets("Sheet2").Range("A:A").Find(Target.Value)
    If nc Is Nothing Then Exit Sub
    Sheets("Sheet2").Activate
    nc.Select
    
End Sub

I assumed your values are all in column A, and that the values on Sheet2 are also in A. If they're in another column, change the A:A references as needed. Now repeat the process and put the same macro on Sheet2, but change the 2 references of Sheet2 to Sheet1.

Now close the VBA editor, go back to your workbook. Now when you double-click on that column, it will do as you want. We can set it up with a single-click if you want, but that really tends to be disruptive. It prevents you from changing anything in that column, or even accidentally putting the cursor there.

Hope this helps!

 

[poli9]

THANK YOU SO MUCH ERIC!!! MERCI BEAUCOUP ERIC (Eric is a french first name correct?) Your explanation is very clear and your code is simple and beautiful!
Have a nice day!
Take care

 

[Eric W]

De rien!

Glad it works for you.

(Although I think Eric is more of a Norse name, remember Eric the Red?)