Formula to point to another cell if an option is chosen.

[Peggy]

Hi,

I have set up a list in data validation. One of the options is 'other'. I
have locked the spreadsheet, apart from the cells I would like the user's to
populate. What I would like to do is have the cursor jump to another section
of the spreadsheet where they can write more comments about this 'other'
option.

Can you help?

Kind regards,
Peggy

 

[Peggy]

Can anyone help with my query?

 

[Gord Dibben]

Formulas cannot make the cursor "jump" to another cell.

You would need event code to move the cursor.

A1 has your dropdown and J1 is where you want to enter the comments.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error GoTo endit
If Target.Value = "other" Then
MsgBox "Please enter comments in J1"
Me.Range("J1").Select
End If
endit:
Application.EnableEvents = True
End Sub


This is sheet event code.

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP