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]

On the second part
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.

Where you want the number to appear? If you want it to replace B2, C2, D2, etc, it is impossible to relate A4, A5, etc to whichever column I think.

 

[adurham22926192]

On the second part
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.

Where you want the number to appear? If you want it to replace B2, C2, D2, etc, it is impossible to relate A4, A5, etc to whichever column I think.

when A4 in sheet 1 is clicked, I want that value to appear in A2 in sheet 1. But then if I click on A5 in sheet 1, I want the number showing in A2 to change to the value that I’ve just clicked. So basically the cell that I’ve clicked, I want that value to be in A2. Hope this makes sense

 

[Zot]

when A4 in sheet 1 is clicked, I want that value to appear in A2 in sheet 1. But then if I click on A5 in sheet 1, I want the number showing in A2 to change to the value that I’ve just clicked. So basically the cell that I’ve clicked, I want that value to be in A2. Hope this makes sense

I misunderstood. I thought it will go to other column ?

 

[adurham22926192]

I misunderstood. I thought it will go to other column ?

lol! Do you know if you could help me?

 

[Zot]

Put this code under Worksheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws2 As Worksheet

Set ws2 = ActiveWorkbook.Sheets("Sheet2")

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

End Sub

 

[Zot]

The same operation like column A will happen for column B, C and D too.

 

[adurham22926192]

Hiya. The code works but I forgot to mention that when I click B4 in sheet 1 I want it to vlookup A4 in sheet 1 in the range A2:B5 in sheet 2 and return the points in B2 on sheet 1 that matches the number so in this case it would be 100 because Judge 1 gave number 1 100 points. But at the same time when B4 is clicked, I want it to vlookup A4 in sheet 1 in the range A2:C5 in sheet and return the points in C2 on sheet 1 that matches the number so it would also be 100 because Judge 2 gave number 1 100 points and I want to do the same thing for Judge 3 so it would lookup the A4 in sheet 1 in the range A2:D5 in sheet 2 and return the points in D2.

When I click B4, I want the B2, C2 and D2 values to all pop up at the same time. Then when I click B5, I want it to do the same vlookup and return those values.

 

[Zot]

Hiya. The code works but I forgot to mention that when I click B4 in sheet 1 I want it to vlookup A4 in sheet 1 in the range A2:B5 in sheet 2 and return the points in B2 on sheet 1 that matches the number so in this case it would be 100 because Judge 1 gave number 1 100 points. But at the same time when B4 is clicked, I want it to vlookup A4 in sheet 1 in the range A2:C5 in sheet and return the points in C2 on sheet 1 that matches the number so it would also be 100 because Judge 2 gave number 1 100 points and I want to do the same thing for Judge 3 so it would lookup the A4 in sheet 1 in the range A2:D5 in sheet 2 and return the points in D2.

When I click B4, I want the B2, C2 and D2 values to all pop up at the same time. Then when I click B5, I want it to do the same vlookup and return those values.

You meant only column B will be clicked and will update B2, C2, D2?

 

[adurham22926192]

You meant only column B will be clicked and will update B2, C2, D2?

Yes