Automatic generation of input data

[dekion]

Hi

A friend of mine asked me if I could help him around with following case.

He's in a card club and they have about + 30 members.

Members join beginning of January and play for a complete year. The play once a month cards together and he notes down all the scores. Every month they rank the best player and the person who wins the most is at the end the champion.

Now I can easily create a sheet that does following:

Name	Month	Games won	Points
John	jan	3	450
Will	jan	2	400
Gloria	jan	1	360
John	feb	1	200
Will	jan	1	320
...	...	...	...
John	dec	1	100

But I would like to avoid to recreate every year this table and I'm trying to find a way to automate all the participants and date to automatically create beginning of the year.
So he only needs to maintain 1 list of participants and automatically everything gets generated, but I wan't to avoid VBA.

The person isn't a Excel expert, so I really want to keep it simple, but I'm lost in my design idea.

Any ideas how I could start with this?

Off course I'll add validation and list where possible so the error margin is reduced to almost none

Thank you for the feedback

 

[ValuationMan]

I don't have all of the details of what you want to accomplish, but in my mind there are two main approaches: Power Query or a combination of Spilled/Dynamic formulas. In both cases, you would want to make sure your data is a structured Table.

Power Query would be nice because you could use the GroupBy feature. If you need to award points for the top scorers by month, you could end up doing some fun things with Index columns. The nice part about the Power Query approach is you create the steps and all that the friend has to do is refresh after data is entered.

If you want the formula approach, I would end by using the UNIQUE and SORTBY functions and do a number of SUMIFS, utilizing the # operator in your criteria.

If neither one of those suggestions make sense, let me know.