1 drop down box Changes another

[ScottyG]

I have 2 dropdown boxes side by side, each with 2 selection choices. The left one: First Name, Home Phone. The right one: Last Name, Work Phone.

Is there a formula so that if the user selects an item from either box, the corresponding item will be entered in the other box, thus saving them one mouse click?

Thanks for any help!

Scotty

 

[HalfAce]

Hi Scotty,
I'm not real clear on your setup, and therefore the direction to take to get what you want.

You have "2 dropdown boxes side by side" - are these:
1) Data Validation cells? - ComboBoxes? - or (?) For that matter, are they on the worksheet
itself or on a userform?
2) They each have only two choices?
The actual text "First Name" & "Home Phone" - and "Last Name" & "Work Phone" or are the
choices actual names and phone numbers?

 

[tusharm]

Like HalfAce, I am not sure what exactly the drop down boxes contain. But for dependent dropdown boxes that work with relational tables see
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

I have 2 dropdown boxes side by side, each with 2 selection choices. The left one: First Name, Home Phone. The right one: Last Name, Work Phone.

Is there a formula so that if the user selects an item from either box, the corresponding item will be entered in the other box, thus saving them one mouse click?

Thanks for any help!

Scotty

 

[ScottyG]

RE: 2 drop down boxes

Sorry, to be more specific:

Both dropdown boxes are on worksheets using Data-Validation-Lists

The Choices are to be used as Headings. Example: Column A would be Either First Name or Home Phone. Then below that, the user would type in the info for whichever choice they made.

If they selected the first item (First Name), then In Column B, I would want the first choice to automatically be selected (Last Name) to be used as a heading, then below that the user would type in the Last Name.

If they selected the second choice (Home Phone) from column A, then the second choice would be selected from column B (Work Phone)

And visa versa, if they selected the right column dropdown box first.

Does that make sense?

 

[tusharm]

Re: RE: 2 drop down boxes

Did you check the tutorial at the link I posted?

Sorry, to be more specific:

Both dropdown boxes are on worksheets using Data-Validation-Lists

The Choices are to be used as Headings. Example: Column A would be Either First Name or Home Phone. Then below that, the user would type in the info for whichever choice they made.

If they selected the first item (First Name), then In Column B, I would want the first choice to automatically be selected (Last Name) to be used as a heading, then below that the user would type in the Last Name.

If they selected the second choice (Home Phone) from column A, then the second choice would be selected from column B (Work Phone)

And visa versa, if they selected the right column dropdown box first.

Does that make sense?

 

[ScottyG]

Re: 2 dropdowns

Tusharm, yes I did look at it and found it very confusing, didn't understand it. I'm also not sure that was the same as what I want.

 

[HalfAce]

OK, if I understand what you're after you might try this in the sheet module.
Assumes:
A1 is a data validation cell with the choices "First Name" and "Home Phone" and
B1 is a data validation cell with the choices "Last Name" and "Work Phone".

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub

If Target.Address = "$A$1" Then
  Application.EnableEvents = False
  Select Case Target.Value
    Case "First Name"
      [B1].Value = "Last Name"
    Case "Home Phone"
      [B1].Value = "Work Phone"
  End Select
End If

If Target.Address = "$B$1" Then
  Application.EnableEvents = False
  Select Case Target.Value
    Case "Last Name"
      [A1].Value = "First Name"
    Case "Work Phone"
      [A1].Value = "Home Phone"
  End Select
End If

Application.EnableEvents = True
End Sub

 

[ScottyG]

Re: 2 drop down boxes

HalfAce - AWESOME! Works perfect. That is exactly what I needed.

I can't thank you enough. Thank you so much for sticking it out and for all of your help.

Scotty

 

[ScottyG]

Problem with Merged cells?

Not sure if I should just make this a new post or not but...

This works perfect as is for cells A1:B1 but the actual cells are merged cells: Y1 and AC1

So I substituted the new letters but now it doesn't work. Any advice?



Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub

If Target.Address = "$A$1" Then
Application.EnableEvents = False
Select Case Target.Value
Case "First Name"
[B1].Value = "Last Name"
Case "Home Phone"
[B1].Value = "Work Phone"
End Select
End If

If Target.Address = "$B$1" Then
Application.EnableEvents = False
Select Case Target.Value
Case "Last Name"
[A1].Value = "First Name"
Case "Work Phone"
[A1].Value = "Home Phone"
End Select
End If

Application.EnableEvents = True
End Sub

 

[Norie]

Scotty

Do you really need the cells merged?

Merged cells just cause problems when you are using code, as you have seen.