Autopopulate Survey result from another sheet

[FREDO]

I have a general Tourist survey result in shteet 1, I want to auto populate other sheets with data from sheet 1 that looks up to some criteria I need like sheet2 "By certain Nationality", sheet3 "By Age Bracket" , and sheet4 "by:Sex & Age" as shown below.I use window 7 and office 2007. I am not familiar with codes so an excel formula to do this would be great unless its not possible. Thanks in advance for any help, appreciate that.

THIS SHEET 1 - TOURIST SURVEY DATA (SAMPLE)

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Tourism Arriavl Survey -Data
2	No.	Survey No.	Days Stay	NAME/ OPTIONAL	NATIONALITY	AGE	SEX	Survey Question 1	Survey Question 2	Survey Question 3	Survey Question 4	Survey Question 5	Survey Question 6	Survey Question 7	Survey Question 8	Survey Question 9	Survey Question 10	Total Ansered
3	1	S001	1	DOMINGO LUNAS	Singapore	45	Male	Yes	No		1		A					4
4	2	S002	1	MARIO CUENCA	Malaysia	51	Male	No	No		1		A					4
5	3	S003	1	SAMUEL SALES	Philippines	25	Male	Yes	No		1		A					4
6	4	S004	1	CHUCKIE LASCON	Philippines	24	Male	Yes	No		1							3
7	5	S005	3	DONG ORTIZ	Britain	60	Male	Yes	No		1		B					4
8	6	S006	3	NORMA AUSTRIA/CARACOL	Britain	36	Female	Yes	Maybe		1		A					4
9	7	S007	5	JOCELYN BUNDA/VILLANUEVA	India	55	Female	Yes	No		2		B					4
10	8	S008	2	JERACEL WONG	Singapore	25	Female	No	No		1		A					4
11	9	S009	2	VISITACION BEGONIA	Singapore	25	Female	Yes	No		1		B					4
12	10	S010	2	HARLET ENCONTRO	Singapore	21	Female	Yes	Yes		2		A					4
13	11	S011	2	MYRNA PABLICO	Singapore	30	Female	Yes	Yes		1		A					4
14	12	S012	1	JESSEL PATRICIO	Philippines	44	Male	No	No		1		A					4
15	13	S013	1	LUCITA CAMENA	Philippines	43	Female	No	No		1		B					4
16	14	S014	1	MELVIN AVANCION	Philippines	23	Male	Yes	No		2		B					4
17	15	S015	4	SHERYL MARAVILLAS	Cambodia	34	Female	Yes	No		2		C					4
18	16	S016	4	PJ ACOSTA	Cambodia	37	Male	Yes	Maybe		1		A					4
19	17	S017	3	OES SANVIC	Philippines	33	Male	Yes	Not sure		1		A					4
20	18	S018	3	OES SANVIC	Philippines	22	Female	Yes	No		1		D					4
21	19	S019	3	DANTE MEKITPEKIT	Australia	65	Male	Yes	No		1							3
22	20	S020	3	CHERRYL TINEBRO	Australia	57	Female	Yes	Maybe		1							3
23	21	S021	3	RENALYN TOQUERO	Australia	38	Female	Yes	Maybe		1		A					4
24	22	S022	3	D. BIGOY	Australia	33	Male	Yes	Maybe		1		A					4
25	23	S023	1	HONEY PAN2	Hongkong	24	Female	Yes	Maybe		1							3
26	24	S024	1	NO NAME	Hongkong	25	Male	Yes	Not sure		1		C					4
27	25	S025	2	OES SANVIC	UAE	36	Male	Yes	Yes									2
28	26	S026	2	JUDY MENDOZA	UAE	34	Female	Yes	Yes		1		C					4
29	27
30	28
31	29
32	30
33	Total Who Answered					26	26	0	25	0	21	0	0	0	0	98

<tbody>
</tbody>

This following sheets below are reports from the data from table above.

SHEET 2 - Was looked up/sorted by Nationality (Australia) from Sheet 1.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Tourism Arrival Survey	BY: NATIONALITY (AUSTRALIA)
2	No.	Survey No.	Days Stay	NAME/ OPTIONAL	NATIONALITY	AGE	SEX	Survey Question 1	Survey Question 2	Survey Question 3	Survey Question 4	Survey Question 5	Survey Question 6	Survey Question 7	Survey Question 8	Survey Question 9	Survey Question 10	Total Ansered
3	1	S019	3	DANTE MEKITPEKIT	Australia	65	Male	Yes	No		1							3
4	2	S020	3	CHERRYL TINEBRO	Australia	57	Female	Yes	Maybe		1							3
5	3	S021	3	RENALYN TOQUERO	Australia	38	Female	Yes	Maybe		1		A					4
6	4	S022	3	D. BIGOY	Australia	33	Male	Yes	Maybe		1		A					4
7	5
8	6
9	7
10	8
11	Total Who Answered					26	26	0	25	0	21	0	0	0	0	14

<tbody>
</tbody>

SHEET 3 - Was looked up/sorted by age bracket which is above age30 from Sheet 1.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Tourism Arriavl Survey	BY: AGE (ABOVE 30)
2	No.	Survey No.	Days Stay	NAME/ OPTIONAL	NATIONALITY	AGE	SEX	Survey Question 1	Survey Question 2	Survey Question 3	Survey Question 4	Survey Question 5	Survey Question 6	Survey Question 7	Survey Question 8	Survey Question 9	Survey Question 10	Total Ansered
3	1	S011	2	MYRNA PABLICO	Singapore	30	Female	Yes	Yes		1		A					4
4	2	S022	3	D. BIGOY	Australia	33	Male	Yes	Maybe		1		A					4
5	3	S017	3	OES SANVIC	Philippines	33	Male	Yes	Not sure		1		A					4
6	4	S015	4	SHERYL MARAVILLAS	Cambodia	34	Female	Yes	No		2		C					4
7	5	S026	2	JUDY MENDOZA	UAE	34	Female	Yes	Yes		1		C					4
8	6	S006	3	NORMA AUSTRIA/CARACOL	Britain	36	Female	Yes	Maybe		1		A					4
9	7	S025	2	OES SANVIC	UAE	36	Male	Yes	Yes									2
10	8	S016	4	PJ ACOSTA	Cambodia	37	Male	Yes	Maybe		1		A					4
11	9	S021	3	RENALYN TOQUERO	Australia	38	Female	Yes	Maybe		1		A					4
12	10	S013	1	LUCITA CAMENA	Philippines	43	Female	No	No		1		B					4
13	11	S012	1	JESSEL PATRICIO	Philippines	44	Male	No	No		1		A					4
14	12	S001	1	DOMINGO LUNAS	Singapore	45	Male	Yes	No		1		A					4
15	13	S002	1	MARIO CUENCA	Malaysia	51	Male	No	No		1		A					4
16	14	S007	5	JOCELYN BUNDA/VILLANUEVA	India	55	Female	Yes	No		2		B					4
17	15	S020	3	CHERRYL TINEBRO	Australia	57	Female	Yes	Maybe		1							3
18	16	S005	3	DONG ORTIZ	Britain	60	Male	Yes	No		1		B					4
19	17	S019	3	DANTE MEKITPEKIT	Australia	65	Male	Yes	No		1							3
20	18

<tbody>
</tbody>

SHEET 4 - Was looked up/sorted by Age above 30 and only females from Sheet 1.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

TOURISM ARRIVAL SURVEY

BY: SEX & AGE (FEMALE RESPONDENT AGE 30 AND ABOVE)

2

No.

Survey No.

Days Stay

NAME/ OPTIONAL

NATIONALITY

AGE

SEX

Survey Question 1

Survey Question 2

Survey Question 3

Survey Question 4

Survey Question 5

Survey Question 6

Survey Question 7

Survey Question 8

Survey Question 9

Survey Question 10

Total Ansered

3

1

S011

2

MYRNA PABLICO

Singapore

30

Female

Yes

Yes

1

A

4

4

2

S015

4

SHERYL MARAVILLAS

Cambodia

34

Female

Yes

No

2

C

4

5

3

S026

2

JUDY MENDOZA

UAE

34

Female

Yes

Yes

1

C

4

6

4

S006

3

NORMA AUSTRIA/CARACOL

Britain

36

Female

Yes

Maybe

1

A

4

7

5

S021

3

RENALYN TOQUERO

Australia

38

Female

Yes

Maybe

1

A

4

8

6

S013

1

LUCITA CAMENA

Philippines

43

Female

No

No

1

B

4

9

7

S007

5

JOCELYN BUNDA/VILLANUEVA

India

55

Female

Yes

No

2

B

4

10

8

S020

3

CHERRYL TINEBRO

Australia

57

Female

Yes

Maybe

1

3

11

9

12

10

13

11

<tbody>
</tbody>

I hope somebody can help.

Thanks
Fredo

 

[Legacy 234512]

hi, it seems like you could use a Pivot Table for this.

 

[FREDO]

hi, it seems like you could use a Pivot Table for this.

Its a shame Im not also familiar with pivot table but I read from other comments that pivot table is good only for values not on data.

Anyway thanks for the suggestion I'll try to learn some things about pivot table.

I still hope anybody here has a ready formula I can use for this. thanks.

 

[FREDO]

What I read was true, or so it seemed, Pivot Tables are only good for values and not for listing data that we need in a report. I just tried the excel 2007 pivot tables last night and it was very annoying. I dont want the tallies or totals, I need the list more. I cant make a list of data I need as result(as posted above).

Please help. It is not even important to tally or sum up the survey result( I can manage that) what i need is to come up with a way to bring those data in another sheet filtered as to Nationality in sheet 2, Age bracket in sheet 3 and Sexual orientation in sheet 4 and so on.

Its like Autofilter but the result will be on the other sheet.

Thank you for any solution you can suggest.

Fredo