Car show Ballot Help

M

Mr Football

New Member
Joined
Sep 16, 2018
Messages
2
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Mr Football,
With the setup shown below:
Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]
Top​
[/TD]
[TD="bgcolor: #E46D0A"]
Best​
[/TD]
[TD="bgcolor: #E6B9B8"]
Best​
[/TD]
[TD="bgcolor: #00FF00"]
Scouts​
[/TD]
[TD]
RANKINGS​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]
30​
[/TD]
[TD="bgcolor: #E46D0A"]
Paint​
[/TD]
[TD="bgcolor: #E6B9B8"]
Show​
[/TD]
[TD="bgcolor: #00FF00"]
Choice​
[/TD]
[TD="bgcolor: #FFFF00"]
Top 30​
[/TD]
[TD="bgcolor: #E46D0A"]
Paint​
[/TD]
[TD="bgcolor: #E6B9B8"]
Show​
[/TD]
[TD="bgcolor: #00FF00"]
Choice​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]Car1[/TD]
[TD="bgcolor: #FFFF00"]
2​
[/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"]
3​
[/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #FFFF00"]
2​
[/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"]
1​
[/TD]
[TD="bgcolor: #00FF00"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]Car2[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"]
1​
[/TD]
[TD="bgcolor: #E6B9B8"]
1​
[/TD]
[TD="bgcolor: #00FF00"]
2​
[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"]
2​
[/TD]
[TD="bgcolor: #E6B9B8"]
3​
[/TD]
[TD="bgcolor: #00FF00"]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]Car4[/TD]
[TD="bgcolor: #FFFF00"]
3​
[/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #FFFF00"]
1​
[/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"]
2​
[/TD]
[TD="bgcolor: #E6B9B8"]
2​
[/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"]
1​
[/TD]
[TD="bgcolor: #E6B9B8"]
2​
[/TD]
[TD="bgcolor: #00FF00"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD]Car10[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"]
5​
[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #E46D0A"][/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: #00FF00"]
1​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

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

Code: 
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
 
Upvote 0
Here is a better shot of the working Car Show Tally Sheet using Imgur.com; sorry for the mess above:

VRGS3.jpg
dcOjold.jpg


Perpa
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Car show Ballot Part II
Replies
0
Views
548
Mr Football
M
M
  • Question Question
Quilt Show Winners - Help!
Replies
3
Views
692
Michael M
Michael M
Oakie_Uffda
  • Question Question
Split Cell into multiple without splitting words and print decent
Replies
1
Views
1K
Oakie_Uffda
Oakie_Uffda
R
  • Question Question
Championship Points work book
Replies
0
Views
727
Raceme49
R
C
  • Question Question
Optimization/VBA Help
Replies
0
Views
694
cmacd
C

Forum statistics

Threads
1,223,714
Messages
6,174,044
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top