Results 1 to 10 of 10

Thread: Booking Form to Create Registers of Attendees

  1. #1

    Booking Form to Create Registers of Attendees



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Click image for larger version. 

Name:	untitled.jpg 
Views:	46 
Size:	91.9 KB 
ID:	965

  2. #2
    It would be easy with VBA, but you cannot really expect us to recreate your data, so post an example workbook.

  3. #3
    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
    Attached Files Attached Files
    Last edited by ben8519; 2012-12-13 at 10:54 AM. Reason: Wrong attachment

  4. #4
    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.

  5. #5
    Yes totally open to a re design.

    Thanks

  6. #6
    What s the difference between a booked day and an owed day? Isn't a booked day owed? And does C signify a cancel?

  7. #7
    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

  8. #8
    What does F stand for, and what version of Excel do you have?

  9. #9
    F is for funded but is not needed. I am using excel 2003

  10. #10
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •