How is this possible...

  • Thread starter Thread starter Warner
  • Start date Start date
W

Warner

I was looking in an excel disk filled with examples for
excel functionality. This is what I saw and want to learn
how it is done...

I saw a list of names from A1:A5. Each cell had a name
like Dave, Mike, etc. When I clicked on the name,
information popped up about them in several cells below
(A10). For example, if I clicked on cell A2 (Dave), then
in cell A10, it would show his address. Then if I clicked
on A5 (Mike) then it would show his address in same cell
A10.

I think this is done in VBA but don't know how it is done.

Anyone know. This functionality rocks!

Thanks
 
This is done with a macro.

As an illustration:

On Sheet1, enter "Dave" and "Bob" without the quotes in A3 and A4.

Select A3:A4 and name the range "Names".

On Sheet2, enter "Bob" and "Dave" in A7 and A8. Enter "Bob's address" and
"Dave's address" in B7 and B8.

Select A7:B8 and name the range "NamesAndAddrs".

Go back to Sheet1. Right-click your worksheet tab, click View Code, and
paste the following code into the code pane:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Names")) Is Nothing Then
Range("A10") = WorksheetFunction.VLookup(Target, _
Sheet2.Range("NamesAndAddrs"), 2, False)
Else
Range("A10") = ""
End If
End Sub

Try clicking on and off the names on Sheet1 and watch A10.
 
Back
Top