When cell is clicked, vlookup that cell

[adurham22926192]

I’m creating a scoring system for dance competitions and I need some help. Basically each Dancer gets a score out of 300 for each of their 3 dances. Here’s a preview of what it looks like.

Sheet 1

	A	B	C	D	E	F
1	Number	Dance 1	Dance 2	Dance 3	Total	Place
2
3	Number	Dance 1	Dance 2	Dance 3	Total	Place
4	1	300
5	3	243
6	2	235
7	4	227

Sheet 2 (vlookup sheet)

Number	Judge 1	Judge 2	Judge 3	Total
1	100	100	100	300
2	71	78	86	235
3	86	86	71	243
4	78	71	78	227

When B4 is clicked I want to vlookup A4 (sheet 1) in Sheet 2 and return Judge 1’s mark that matches the number (100) in cell B2. So for example when I click on cell B5 in Sheet 1, it looks up the number in the range (A2:A5) on sheet 2 and returns the second column data which for this case would be 86 because 3 is the number in A5 in Sheet 1.

Also, when A4 is clicked, I want the value in A4 to appear but then if I click on A5, I want that value to show. So it changes every time you click a different number.

Hope this makes sense! Please help

 

[Zot]

Something like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws2 As Worksheet

Set ws2 = ActiveWorkbook.Sheets("Sheet2")

If Not Intersect(Target, Range("A4", "A7")) Is Nothing Then
    Cells(2, Target.Column) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column, 0)
End If

If Not Intersect(Target, Range("B4", "B7")) Is Nothing Then
    Cells(2, Target.Column) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column, 0)
    Cells(2, Target.Column + 1) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column + 1, 0)
    Cells(2, Target.Column + 2) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column + 2, 0)
End If

End Sub

 

[adurham22926192]

Something like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws2 As Worksheet

Set ws2 = ActiveWorkbook.Sheets("Sheet2")

If Not Intersect(Target, Range("A4", "A7")) Is Nothing Then
    Cells(2, Target.Column) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column, 0)
End If

If Not Intersect(Target, Range("B4", "B7")) Is Nothing Then
    Cells(2, Target.Column) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column, 0)
    Cells(2, Target.Column + 1) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column + 1, 0)
    Cells(2, Target.Column + 2) = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), Target.Column + 2, 0)
End If

End Sub

Hi. That code works perfectly but I forgot to add something. Say for example B4 in sheet 1 is clicked, the vlookup values appear in B2, C2 and D2. Then when I click A4 in sheet 1, that value comes up in A2. this is what I wanted.

But when I click a new cell, say for example B5, I want the value that’s in A2 which is the previous vlookup number to disappear so that when I click A5, the new vlookup result appears. I want this to work for all the 3 columps so B4:B7, C4:C7 and D4:D7

Does this make sense?

 

[Zot]

I hope my understanding is right

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim strCol As String
Dim ws2 As Worksheet

Set ws2 = ActiveWorkbook.Sheets("Sheet2")

If Not Intersect(Target, Range("A4", "A7")) Is Nothing Then
    Cells(2, "A") = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), 1, 0)
End If

If Not Intersect(Target, Union(Range("B4", "B7"), Range("C4", "C7"), Range("D4", "D7"))) Is Nothing Then
    Cells(2, "A") = ""
    Cells(2, "B") = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), 2, 0)
    Cells(2, "C") = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), 3, 0)
    Cells(2, "D") = Application.WorksheetFunction.VLookup(Range("A" & Target.Row), ws2.Range("A1", "D5"), 4, 0)
End If

End Sub