1 drop down box Changes another

ScottyG

Board Regular
Joined
Mar 30, 2006
Messages
62
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,023
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

Board Regular
Joined
Mar 30, 2006
Messages
62
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,023

ADVERTISEMENT

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

Board Regular
Joined
Mar 30, 2006
Messages
62
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

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".
Code:
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

Board Regular
Joined
Mar 30, 2006
Messages
62
Re: 2 drop down boxes

HalfAce - AWESOME! :biggrin: 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

Board Regular
Joined
Mar 30, 2006
Messages
62
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Scotty

Do you really need the cells merged?

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

Watch MrExcel Video

Forum statistics

Threads
1,112,884
Messages
5,543,023
Members
410,583
Latest member
gazz57
Top