Collect data from 4 sheets, sort, then paste.

mj_bowen

Board Regular
Joined
Oct 4, 2009
Messages
99
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
You could instead put all the classes on a single sheet, and then use a pivot table.
 

mj_bowen

Board Regular
Joined
Oct 4, 2009
Messages
99
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

Board Regular
Joined
Oct 4, 2009
Messages
99

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,397
Messages
5,601,434
Members
414,450
Latest member
Cassy_sn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top