Enter value in 1 cell, auto update another

[ABabeNChrist]

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.

[HansV]

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.

[ABabeNChrist]

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

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

[HansV]

You can shorten code such as

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

to

Code: Select all

        Sheets("Sheet2").Range("A1:A3").Value = Range("A4:A6").Value

[ABabeNChrist]

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"

[HansV]

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.

[ABabeNChrist]

Thank you Hans
Thats a great idea
I thank you so much for your most greatly appreciated assistance.......

[HansV]

Here is an example from the Advanced section of the Excel Options dialog:

x231.png

[ABabeNChrist]

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