Automatically Updating a cell in Excel from another sheet's data, based on another cell's value

Question

So, I have setup a drop down menu with a list of names in A1. What I want to happen, is have the Data in F34 change based on what A1 is reading. I want to have F34 update with data from another sheet.

The data would look something like this:

• Name 1 Corresponds with data on Sheet2!C6
• Name 2 Corresponds with data on Sheet2!C7
• Name 3 Corresponds with data on Sheet2!C8
• And so on.

What I tried was something like this. F34: =IF(A1="Name 1",Sheet2!C6), (A1="NAME 2",SHEET2!C7) And so on.

But I am not getting anywhere. Any ideas on what this would look like?

Answer 1

How do you populate the drop-down? Did you write the names into the dialog, or is there a list of names in the workbook somewhere?

You could:

• insert a column on Sheet2 before column C and enter the names into this column, so they are next to the values they relate to. These values are now in column D.
• use a Vlookup to find the related info with a formula like

=vlookup(A1,Sheet2!$C$6:$D$20,2,False)

If you cannot /don't want to put the list of names next to the other info on sheet 2, put the list of names anywhere, say in cells Z1 to Z20 on sheet 1. Then you could use a formula like

=index(Sheet2!$C$6:$c$20,match(A1,$Z$1:$Z$20,0))

Answer 2

You can do this in VBA via event Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    // do a switch statment here, where you assign F34 value
    End If 
End If
End Sub

Answer 3

Your desired functionality can be achieved without VBA. Let's build the sheet step by step:

We need the value for the dropdown that will show up in A1. As you already wrote, there is a sheet (I name it roster). Here we enter the value for the dropdown and in the cell right next to it the desired address where you want to get the data from:

  |  A     |    B      |
  +--------+-----------+
1 | Name_1 | Sheet2!C6 |
2 | Name_2 | Sheet2!C7 |
3 | Name_3 | Sheet2!C8 |

Go to Cell A1 and set the validity to "Allow cell range" and set the cell range to roster.$A$1:$A$3. You now have the dropdown.

As the next step, let's enter the value in Sheet2: go to Sheet2 and enter the value to retrieve (I just use some stupid texts :-) )

  | A  | B  |   C   |
  +----+----+-------+
6 |    |    | Alpha |
7 |    |    | Beta  | 
8 |    |    | Gamma |

Now for the interesting part: how to display the values depending on your dropdown selection? We use a combination of Indirect() and VLookup():

Go to your cell F34 and enter the formula below:

=INDIRECT(VLOOKUP(A1,roster.$A$2:$B$6,2,FALSE()))

What's going on?

• The inner part VLookup(), searches for the value of A1 in the matrix where we defined the valid vlaues and the addresses for the data, takes the second column (the addr) and retrieves it. So if you have "Name_2" in the dropdown, this function will return Sheet2!C7
• The outer part uses this address via the Indirect() function to retrieve from this address (who said Excel doesn't know Pointers), so you get the text "Beta".

So all we need is a matrix with valid value, the addresses of the data to retrieve and a single formula. Hope that answers your question, though I'm a bit late to the party, if I look at the timestamp. :-)