I’m trying to figure out the best approach that will allow me to enter a name in one cell and then would automatically update the 2 following cells with 1 cell being a phone number and the other cell being an email address that matches the selected name. I thought of using conditional formatting. I also would like for it to be easily updatable.
Reason for this is I have a quite a few of realtors I work with on a regular basis and of course some come and go, I was trying to simplify a method to enter their info without having to look it up each time.
Enter value in 1 cell, auto update another
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Enter value in 1 cell, auto update another
Conditional formatting, as the name implies, only affects cell formatting, it cannot be used to change the value of a cell.
You have two options:
1) Use lookup formulas (involving VLOOKUP or INDEX and MATCH) for the phone number and e-mail address
- or -
2) Use the Worksheet_Change event procedure to populate the phone number and e-mail address.
You have two options:
1) Use lookup formulas (involving VLOOKUP or INDEX and MATCH) for the phone number and e-mail address
- or -
2) Use the Worksheet_Change event procedure to populate the phone number and e-mail address.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Enter value in 1 cell, auto update another
Hi Hans
I was playing around with some code and thought about using a separate sheet that I could use to populate with my realtors. I could then layout the sheet using checkboxes in front of each one, then using a command button. Here is the code I was able to come up with
I was playing around with some code and thought about using a separate sheet that I could use to populate with my realtors. I could then layout the sheet using checkboxes in front of each one, then using a command button. Here is the code I was able to come up with
Code: Select all
Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then
'This will copy the value from sheet1 over to sheet2
'realtor 1
Sheets("Sheet2").Range("A1").Value = Range("A1").Value 'Name
Sheets("Sheet2").Range("A2").Value = Range("A2").Value 'Phone
Sheets("Sheet2").Range("A3").Value = Range("A3").Value 'Email
End If
If CheckBox2.Value = True Then
'realtor 2
Sheets("Sheet2").Range("A1").Value = Range("A4").Value 'Name
Sheets("Sheet2").Range("A2").Value = Range("A5").Value 'Phone
Sheets("Sheet2").Range("A3").Value = Range("A6").Value 'Email
End If
End Sub
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Enter value in 1 cell, auto update another
You can shorten code such as
to
Code: Select all
Sheets("Sheet2").Range("A1").Value = Range("A4").Value 'Name
Sheets("Sheet2").Range("A2").Value = Range("A5").Value 'Phone
Sheets("Sheet2").Range("A3").Value = Range("A6").Value 'Email
Code: Select all
Sheets("Sheet2").Range("A1:A3").Value = Range("A4:A6").Value
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Enter value in 1 cell, auto update another
Hi Hans
Coooooool that will work
As it is at the present moment if 2 checkboxes were to be selected i would then receive an error. Should I add
On Error Go To Message
Msg "You have made 2 or more selections"
Coooooool that will work
As it is at the present moment if 2 checkboxes were to be selected i would then receive an error. Should I add
On Error Go To Message
Msg "You have made 2 or more selections"
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Enter value in 1 cell, auto update another
You should use radio buttons instead of check boxes. The interface convention is that check boxes are used if the user can select more than one option, and radio buttons (aka option buttons) if the options are mutually exclusive.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Enter value in 1 cell, auto update another
Thank you Hans
Thats a great idea
I thank you so much for your most greatly appreciated assistance.......
Thats a great idea
I thank you so much for your most greatly appreciated assistance.......
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Enter value in 1 cell, auto update another
Here is an example from the Advanced section of the Excel Options dialog:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Enter value in 1 cell, auto update another
Hi Hans
I made my changes using Option Button (Form Control) and they worked beautiful
Just what I was looking for
once again many thanks
I made my changes using Option Button (Form Control) and they worked beautiful
Just what I was looking for
once again many thanks