Car show Ballot Help

[Mr Football]

I help with local Boy scout troop that does a car show every year. I am looking for a spreadsheet that can tabulate the winners as we input the ballots.
we expect upwards of 200 entries. The categories are. 1) Top 30. (30 trophies will be awarded) Note this category often has ties. 2) Best Paint. 3) Best in Show. and 4) Scouts Choice.

Thank you

 

[Perpa]

Mr Football,
With the setup shown below:
Excel 2007 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​		Top​	Best​	Best​	Scouts​	RANKINGS​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​		30​	Paint​	Show​	Choice​	Top 30​	Paint​	Show​	Choice​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Car1	2​		3​		2​		1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Car2		1​	1​	2​		2​	3​	2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Car4	3​				1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​			2​	2​			1​	2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	Car10				5​				1​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

To accumulate the ranking in each category as you go,
Put these formulae in cells F3 to I3:
In F3: =IF($B3="","",RANK($B3,$B$3:$B$210))
In G3: =IF($C3="","",RANK($C3,$C$3:$C$210))
In H3: =IF($D3="","",RANK($D3,$D$3:$D$210))
In I3: =IF($E3="","",RANK($E3,$E$3:$E$210))

Then copy the formulae down to the last row.

Put the following code in a Worksheet Module: right click the sheet tab at the bottom of the worksheet, select 'View Code', then change the window from 'General' to 'Worksheet', then paste the code below right over the two lines that are shown. Close the window, and save the workbook as macro-enabled.

Then when you click on a cell in columns B to E the cell value will be increased by 1.
The formulae/values in columns F to I , row 1 to your lastrow, should be locked and then the worksheet protected to prevent accidental selection of a formula cell in the ranking section.
Perpa

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 1 Then Exit Sub
If Target.Column > 5 Then Exit Sub
If Target.Row < 3 Then
     Exit Sub
Else
     Target.Value = Target.Value + 1
End If
cells(1,1).select
End Sub

 

[Perpa]

Here is a better shot of the working Car Show Tally Sheet using Imgur.com; sorry for the mess above:

Perpa