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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

HalfAce

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

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,454

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
76,303
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.
 

Forum statistics

Threads
1,141,427
Messages
5,706,382
Members
421,446
Latest member
rena jhon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top