Macro to complete Excel score sheet.

I hope some of you Excel gurus can help me with this task. Just a little background:
I am a member pf a photographic club. Each month we run a little competition where we lay out and number some prints and a selection of members (between 10 and 12) serve as judges.
Each judge picks their own top 1st, 2nd, 3rd. 4th, and fifth print, Each simply completes a voting slip and writes the photo number next to the placing, so a completed voting slip may look something like this

Place  Photo #
1st      20
2nd     12
3rd      8
4th      32
5th      19

The up to now the secretary has simply taken these slips and by awarding 5 point for 1st, 4 points for 2nd etc has entered them into a spreadsheet like the one on the summary tab in the excel workbook attached.

However this has proven to be very tedious and prone to error. I thought it would be easier simply to enter the photo numbers from the voting slip onto the votes sheet in the attached workbook, and then run a macro that automatically allocated the points to the chosen photos one voting slip per line.

So for example of judge 1 voted
1st      20
2nd     12
3rd      8
4th      32
5th      19

the macro would go to the summary tab and on the Vote 1 row allocate 5 points to Photo 20, 4 points for Photo 12 etc.

The process would then be repeated for each judge, each on a new row.

Sounds good in theory - but how do I make it work?

I hope I've explained it OK, if there are any questions please ask.

I will attach the workbook - (it already as a couple of macros for sorting purposes)
Monthly-Competition.xlsm
LVL 70
Brian PiercePhotographerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
You don't need a macro, a formula can do it.
For B7, copy down and across (only formula).
=IF(SUMPRODUCT((Votes!$E$2:$E$61=$A7)*(Votes!$C$2:$C$61=B$6)*Votes!$F$2:$F$61)=0,"",SUMPRODUCT((Votes!$E$2:$E$61=$A7)*(Votes!$C$2:$C$61=B$6)*Votes!$F$2:$F$61))

Open in new window

2 columns E and F added on the Votes sheet to make text match the Summary sheet, and added Vote 11 and 12.
Hide the columns if you don't want to see them.

To avoid wrong input added Datavalidation to Photo # column to only integers from 1 to 60, and conditional format (red) if photo numbers are used more than once for each vote.
Corrected the 2 sorting macros to address the Summary sheet.

See file
Monthly-Competition.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian PiercePhotographerAuthor Commented:
That's great - just what I was looking for. Thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.