Collect data from 4 sheets, sort, then paste.

[mj_bowen]

Hi,
I am trying to create a spreadsheet that can collect children’s ‘Golden time’ activity choices throughout 4 classes and then transfer these to ‘activity registers’ for the activity leaders.

The idea is that the Activity Leaders fill in the ‘Overview of Activities’ page (there can be up to a maximum of 30 activities, but not all will always be filled in) – this table is then copied to the class pages in the ‘Golden Time’ section (allowing each class to see the choices on offer and the number of spaces left).
So far, I have created drop down menus in Column D for Emerald, Ruby, Gold and Sapphire class (allowing each child’s choice to be entered easily and accurately).

I can create the spreadsheet and make it look the way I think would work best within school, but do not know how to collect all of the activity choices from the four classes and sort these into activity registers so that each leaders knows which children are coming to their activity (and also if they come from Em, Gold, Ruby or Sapphire.

Can this be done? Would it need a special VBA?
Any suggestions would be warmly welcomed.

Please see this workbook example file: https://www.box.com/s/f221d6cb86333e08efb9

Matt

 

[b.downey]

In you enviroment, is the file on a sever were multiple users are accessing the file at the same time? The reason I am asking the question is related to "Spaces". I assume this is the number of spaces available in a class and I am wondering how you stay to this limit

 

[shg]

You could instead put all the classes on a single sheet, and then use a pivot table.

 

[mj_bowen]

Hi b.downey,

Thank you for your reply! Yes, the file will be on a server, however, only one class will be accessing the file at one time. (We have a rota, so as to avoid the same children filling up the same 'spaces' each week) Spaces are set by the activity leaders and can be as few as 10 or as many as 60 children). It would be really useful if the spreadsheet could keep a running total of spaces remaining!

To put this project in to context - The reason I am trying to sort this system out is that the children are 5, 6 and 7 years olds who tend to forget their chosen activities by the time Golden Time arrives on a Friday afternoon!

We have, up till now been using a simple clipboard system, which has been passed from registration class to registration class. The clipboard would have one piece of paper for each activity - which is great for the group leader, but come Friday, we have no record of where the children from each class is going, only lots of pieces of paper with activity lists!

I think I'm on the right track with the layout but any advice would be welcomed,

thank you again for your message,
Matt

 

[mj_bowen]

Hi shg,

thank you for your message, excuse my novice-ness, I have just Googled 'Pivot Sheet', but am not entirely sure how to set one up for this project!

Regards,

Matt

 

[shg]

I'm a tyro at pivot tables, but see the workbook at https://www.box.com/s/f2c155ffe8c327dc7237

If you make changes on the assignment sheet, right-click anywhere in the pvot table data and select Refresh.

Debra Dalgleish is the undisputed queen of pivot tables -- you couldn't go wrong buying one of her books.

 

[mj_bowen]

Hi shg,

Thank you, it works like a charm!

Matt