Need Help Quick

[christ_is_the_one]

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

 

[sbendbuckeye]

Can you post some sample data from your Master sheet including column names. That will make it easier to help.

 

[christ_is_the_one]

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
	A	B	C	D	E	F	G	H
1	Reg#	Name	Sex	Age	Church	Dorm	MotelorCabin	Team
2	1	JohnDoe1	F	Adult	Willow	Motel-202	Motel	Orange
3	2	JohnDoe2	F	Adult	Willow	Pine-R	Cabin	Pink
4	3	JohnDoe3	F	Adult	Willow	Pine-R	Cabin	Green
5	4	JohnDoe4	F	18	Willow	Pine-R	Cabin	Orange
6	5	JohnDoe5	F	12	Willow	Pine-R	Cabin	Aqua
7	6	JohnDoe6	F	Adult	Willow	Pine-R	Cabin	LimeGreen
8	7	JohnDoe7	F	10	Willow	Pine-R	Cabin	LimeGreen
9	8	JohnDoe8	F	16	Willow	Pine-R	Cabin	Aqua
10	9	JohnDoe9	F	17	Willow	Pine-R	Cabin	Green
11	10	JohnDoe10	F	12	Willow	Pine-R	Cabin	LimeGreen
12	11	JohnDoe11	F	16	Willow	Pine-R	Cabin	LimeGreen
13	12	JohnDoe12	F	11	Willow	Pine-R	Cabin	Purple
14	13	JohnDoe13	F	4	Willow	Motel-205	Motel	Purple
15	14	JohnDoe14	F	7	Willow	Pine-R	Cabin	Purple
16	15	JohnDoe15	M	Adult	Willow	Motel-202	Motel	Orange
17	16	JohnDoe16	M	5	Willow	Motel-202	Motel	Orange
18	17	JohnDoe17	M	Adult	Willow	New-R	Cabin	Green
Master Reg List

 

[christ_is_the_one]

This is the sheet that I want to create dynamically from data on the master sheet.

Camp Fees Sheets '04 for web.xls
	A	B	C	D	E	F	G	H
1	Ages0-2
2
3	Reg#	Name	Sex	Age	Church	Dorm	MotelorCabin	Team
4	1	JohnDoe1	F	0	Willow	Motel-202	Motel	Orange
5	2	JohnDoe2	F	1	Willow	Pine-R	Cabin	Pink
6	3	JohnDoe3	F	0	Willow	Pine-R	Cabin	Green
7	4	JohnDoe4	F	1	Willow	Pine-R	Cabin	Orange
8	5	JohnDoe5	F	1	Willow	Pine-R	Cabin	Aqua
9	6	JohnDoe6	F	2	Willow	Pine-R	Cabin	LimeGreen
10	7	JohnDoe7	F	0	Willow	Pine-R	Cabin	LimeGreen
11	8	JohnDoe8	F	1	Willow	Pine-R	Cabin	Aqua
12	9	JohnDoe9	F	1	Willow	Pine-R	Cabin	Green
Sheet1

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

 

[Fergus]

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]

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]

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.

 

[christ_is_the_one]

Heeeellllpppp Anybody?

 

[daniels012]

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]

Actually you would not need to change the form around at all doing it this way!!

Good Luck,
Michael