Booking Form to Create Registers of Attendees

ben8519

New member
Joined
Dec 13, 2012
Messages
5
Reaction score
0
Points
0
Hi

I have a Booking system in place for my children's club where children book in advance. Each child has a table as below. Adding "B" means they have booked and "P" means it has been paid for.

I would like if possible to create a register of children attending each day from these tables. Is there anyway to do this?

Thanks

Ben

untitled.jpg
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
It would be easy with VBA, but you cannot really expect us to recreate your data, so post an example workbook.
 

ben8519

New member
Joined
Dec 13, 2012
Messages
5
Reaction score
0
Points
0
Sorry not very good at all this. Have attached the workbook have had to change all names for security reasons (to letters of the alphabet). Would just like to create a register of names for each day to avoid having to type them out each day.

Thanks
 

Attachments

  • Copy of Breakfast Club Booking.xls
    200.5 KB · Views: 25
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Ben,

Are you open to a re-design of the input? We can make it far simpler to get all of the info you require as well as easier to maintain.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
What s the difference between a booked day and an owed day? Isn't a booked day owed? And does C signify a cancel?
 

ben8519

New member
Joined
Dec 13, 2012
Messages
5
Reaction score
0
Points
0
Booked is a day that has been booked, Owed day is a day where the child has attended and not payed and C is Cancelled
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
What does F stand for, and what version of Excel do you have?
 

ben8519

New member
Joined
Dec 13, 2012
Messages
5
Reaction score
0
Points
0
F is for funded but is not needed. I am using excel 2003
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Okay, here is a first cut.

The heart is the Bookings worksheet. Here you enter the following details of any bookings, or retrospective attendance (Owed)
-column A: name
-column B: date
-column C: Y if a booking, not owed
-column D: Y if owed
-column E: Y if and when paid
-column F: Y if cancelled
-column G: Y if due (determined from C:F)
-column H: year of date (calculated)
-column I: month of date (calculated)
-column J: date w/c (calculated)
-column K: amount due - bookings not yet paid(calculated)
-column L: amount bookings paid (calculated)
-column M: amount owed (calculated)

The rest is pivotting from here.


Weekly Summary pivot
This is a simple pivot showing counts of all of the categories over date, over w/c, over month, over year.

Attendance
Another pivot that is a list of who is attending/attended for any given week. You select the w/c from the report filter and it shows the list.

Paid
A pivot that summarises the income received, over w/c, over month, over year.

Owed
A pivot that summarises the income owed, over w/c, over month, over year. Unlike Paid, it shows the name owing.

Due
A pivot that summarises the amount due, that is booked not paid or cancelled (not owed), over w/c, over month, over year. Also it shows the name due.

Take a look, see what you think, and, if you like it, how it might be refined.
 

Attachments

  • XLGuru - 1309 - Breakfast Club Booking.zip
    159.5 KB · Views: 32
Top