Complicated attendance tracker/summary

[jimmet]

Hi all,

I have uploaded a sample of my current attendance spreadsheet. As you can see it keeps track of

• The name of the event;
• Who has attended what event;
• Who organised each event and;
• The date of every event;

Ideally I would like to continue to use this spreadsheet further into the future, but it starts to extend too far horizontally and it does not have all the functionality I would like. I would happily use one spreadsheet for each year, but then I would not know how to sort the information below.

I was considering using a pivot table to be able to quickly check who has not filled their attendance quota (attend 6 events a year and organise 1), how many events had been held in certain date periods, whether the 'squires' had attended their required 4 events to become 'knights' and various other information.

I have looked at many other attendance tracking sheets, but these usually are not concerned with dates and organisers of events.

I have exhausted my own knowledge bank in trying to make this happen and if anyone could provide suggestions I would greatly appreciate it.

Thanks

 

[Ronak Parekh]

would this sheet with simple count and vlookup formula work?

 

[jimmet]

Hi Ronak,

Thanks for your solution, it is quite simple, however it still does not solve my lengthy horizontal issue (I could use cell grouping to shorten the data by month, but I would prefer to somehow automate the system) and I am looking for some more in depth summaries.

I have uploaded an updated spreadsheet with their yearly attendance on the main page.

Ideally I would like a table (or one for each criteria) that would populate when certain criteria are filled.
These criteria are:

1. AND(OR("Knight","Squire"),E6<6,[Extra criteria if they have not organised an event in the last year])=TRUE
2. AND("Prospective",D6>4)=TRUE
3. AND(OR("Prospective","Squire"),[criteria for >6 months since their first event])=TRUE

If any of these criteria run true, then I would like there to be a dynamically updated table that would contain 'name, position, individual attendance and yearly attendance'. The advanced filter is good, but it cannot create a dynamic table to my knowledge.

For criteria 1 I would like the table background to be red, for criteria 2 the background to be green and criteria 3 the background to be yellow.

Oh, and this list of people and is going to grow. Is formatting this as a table and then referencing the table the best way to ensure all future data in incorporated?

Sorry for the essays, thanks in advance for you help