How to jump to a cell based on a number in another cell

[asivaraman]

I have a project tracking sheet.

I need to jump to a cell based on the number i fill in another cell.
I have attached an image for visual reference.
When I type number "2" in F5, i need the cell to jump to R5 (i.e Oct-30)

I may create up to 52 weeks so the formula should work accordingly.

Please help. or suggest another easier way?
Thank you

 

[My Aswer Is This]

This script will do what you want in column F starting in row 5.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/18/2020  1:02:24 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 6 And Target.Row > 4 Then
    If Target.Value = "2" Then Target(, 13).Select
End If
End Sub

 

[asivaraman]

Hello there,
Thank you for the script. I see that when I enter number "2" on F6 the cells jumps to the respective week.
But it doesn't work for the other numbers.
If i enter 3 or 4, it should jump to the respective week Nov 6 or Nov 13

Please teach me how to extend the above code based on my request. Thank you again!
Arun

 

[My Aswer Is This]

You need to be more specific.
Tell me if You enter 3 it should jump to what column.
Do not say Nov 6

I have no way of knowing where Nov 6 is.
I cannot tell from your image where Nov 6 is

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "F6" And Target.Value <> "" Then
      Cells(6, (Target - 1) * 5 + 13).Activate
   End If
End Sub

 

[My Aswer Is This]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "F6" And Target.Value <> "" Then
      Cells(6, (Target - 1) * 5 + 13).Activate
   End If
End Sub

If this works I would be interested in how you came to this conclusion with the limited information in this post.

 

[Fluff]

The image shows that each week is 5 columns wide & week 1 starts in col M.

 

[asivaraman]

Mr Fluff
The code works.
Thank you both for helping me out.

I actually extended "My Answer is this" code as the below,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 6 And Target.Row > 4 Then
If Target.Value = "1" Then Target(, 8).Select
If Target.Value = "2" Then Target(, 13).Select
If Target.Value = "3" Then Target(, 18).Select
If Target.Value = "4" Then Target(, 23).Select
If Target.Value = "5" Then Target(, 28).Select
If Target.Value = "6" Then Target(, 33).Select
If Target.Value = "7" Then Target(, 38).Select
If Target.Value = "8" Then Target(, 43).Select
If Target.Value = "9" Then Target(, 48).Select
If Target.Value = "10" Then Target(, 53).Select
End If
End Sub

But looks like Mr.FLUFF code is way easier.
Anyways thank you both for this. Amazing!

Arun

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[My Aswer Is This]

If you wanted to use my solution you can try this:
I admit I stole the formula from Fluff.
This is a easy way to write a long if statement Using Case:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/18/2020  11:46:56 PM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 6 And Target.Row > 4 Then
    Select Case Target.Value
        Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10": Target(, Target - 1 * 5 + 13).Select
    End Select
End If
End Sub