Need Help Quick

christ_is_the_one

New Member
Joined
Jan 26, 2005
Messages
8
I have a excell workbook containing multiple sheets for a church camp roster. On sheet is the master list and the others are a list for each church (the other sheets don't have names they just count the ones from the master sheet and return a number).

I want to a few other sheets that take the roster and divide it into classes based on age. One sheet will be for 0-2 year olds and will contain only their names, the next will be for 3-4 yr olds and will contain only their names, etc.

I want these sheets to automatically take the info from the master roster sheet as it changes (as we add new people or take off people) based on these criteria.

The master sheet contains their names, ages, and various other info. I want to be able to take all of this info and put it on these new sheets as I explained above.

Any help is appreciated. Thanks.

Sims
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Can you post some sample data from your Master sheet including column names. That will make it easier to help.
 

christ_is_the_one

New Member
Joined
Jan 26, 2005
Messages
8
This is the master registration sheet. It contains the list of the registrants. It actually contains about 300 names but for the sake of space I kept it this small.
Camp Fees Sheets '04 for web.xls
ABCDEFGH
1Reg#NameSexAgeChurchDormMotelorCabinTeam
21JohnDoe1FAdultWillowMotel-202MotelOrange
32JohnDoe2FAdultWillowPine-RCabinPink
43JohnDoe3FAdultWillowPine-RCabinGreen
54JohnDoe4F18WillowPine-RCabinOrange
65JohnDoe5F12WillowPine-RCabinAqua
76JohnDoe6FAdultWillowPine-RCabinLimeGreen
87JohnDoe7F10WillowPine-RCabinLimeGreen
98JohnDoe8F16WillowPine-RCabinAqua
109JohnDoe9F17WillowPine-RCabinGreen
1110JohnDoe10F12WillowPine-RCabinLimeGreen
1211JohnDoe11F16WillowPine-RCabinLimeGreen
1312JohnDoe12F11WillowPine-RCabinPurple
1413JohnDoe13F4WillowMotel-205MotelPurple
1514JohnDoe14F7WillowPine-RCabinPurple
1615JohnDoe15MAdultWillowMotel-202MotelOrange
1716JohnDoe16M5WillowMotel-202MotelOrange
1817JohnDoe17MAdultWillowNew-RCabinGreen
Master Reg List
 

christ_is_the_one

New Member
Joined
Jan 26, 2005
Messages
8
This is the sheet that I want to create dynamically from data on the master sheet.
Camp Fees Sheets '04 for web.xls
ABCDEFGH
1Ages0-2
2
3Reg#NameSexAgeChurchDormMotelorCabinTeam
41JohnDoe1F0WillowMotel-202MotelOrange
52JohnDoe2F1WillowPine-RCabinPink
63JohnDoe3F0WillowPine-RCabinGreen
74JohnDoe4F1WillowPine-RCabinOrange
85JohnDoe5F1WillowPine-RCabinAqua
96JohnDoe6F2WillowPine-RCabinLimeGreen
107JohnDoe7F0WillowPine-RCabinLimeGreen
118JohnDoe8F1WillowPine-RCabinAqua
129JohnDoe9F1WillowPine-RCabinGreen
Sheet1


I want to create one of these for each class. The list is determined by age and gender
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

Hi there,

What is the determining factor for putting names into the new sheet? On your example sheet1 for Ages 0-2 the people you have shown, i.e. John Does 1 through 9 are shown as ages Adult, Adult, Adult, 18,12,Adult,10,16 and 17 on your Master Reg List but on sheet1 seem to have ages 0,1,0,1,1,2,0,1 and 1.
 

christ_is_the_one

New Member
Joined
Jan 26, 2005
Messages
8
Sorry for the discrepancy. The criteria is the age. I want the second sheet to select its data based on age and gender. In the example above I did it manually and forgot to change the age on the first sheet.

I want the class sheet to take the rows from the master sheet based on age and gender.
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

Hi,

You need vba code for this - not really my strong point, sorry. Hopefully one of the code gurus will see this and help you out.
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
If you can change the form around a bit. This will work!
Sheet 2
1)Put your ages Column in the first row
2)Sort data buy age (The numbers will be in order followed by letters)
3)Now on the second sheet have your headings
skip a line
4) Copy your headings again
Now your ready to do Advanced Filter.
Go to Data... Filter... then Advanced Filter
You will have a menu:
A) Copy to another location
B)List range: is where your main page of data is, highlight it
C)Criteria range: is (above Number 3) first set of headings and the skipped line! In the skipped line area under age put >18 to give Adults.
D)Copy To: is (above Number 4) Your second set of headings and as many records you think will come out for this age group from the main list.
Hit OK and all adults over 18 should show up.

I love this feature! If you have trouble you can go to help. It has good explinations for this process.

Hope this helps,
Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Actually you would not need to change the form around at all doing it this way!!

Good Luck,
Michael :coffee:
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,021
Latest member
naimathulla

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