How to copy specific data from one sheet to another based on a specific value

[antexity]

Please see below table "NCAR"(table sheet name) . I would like to report on statuses. I would like the report in a new sheet called "Status RPT". I only need two columns in the "Status RPT", "CAR Number" (B1 in below Table) and "Status".As well a Drop Down menu in which I can do a Data Validation and make a list of Status that are 1:1 (Escalation 1, Late, Follow-Up etc) In the below sheet, the status columns I need reporting on are (L1, O1, R1, U1, X1, AA1, and AD1)

How I would like to use it. I will click on sheet "Status RPT" and select for example Follow-Up in my drop down I created. The formula will look at the data in the "NCAR" table, look for the status "Follow-Up" in columns (L1, O1, R1, U1, X1, AA1, and AD1) and pump out all the CAR # from B1 Column of "NCAR" and list them in my "Status RPT" sheet. I can then change the status to Cancelled and it will produce the list of Cancelled CAR Number's.

Hopefully this is doable and I really appreciate your time in helping me out. I exhausted all the resources and learn something while doing it but still no luck.
Thanks!




Excel 2010

	A	B	C	D	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	#	CAR Number	Issued By	Department	Issued to	Issued Date	Acknowledgement Due Date	Date Acknowledge	Date Acknowledged on time	Action Plan Due Date	Actual Plan Submission Date	Actual Plan Submission Date on time	Estimated Completion Due Date 1	Actual completion date 1	Act Comp Date 1 Ontime	Estimated Completion Due Date 2	Actual completion date 2	Act Comp Date 2 Ontime	Estimated Completion Due Date 3	Actual Completion Date 3	Act Comp Date 3 Ontime	Estimated Completion Due Date 4	Actual completion date 4	Act Comp Date 4 Ontime	Estimated Completion Due Date 5	Actual Completion Date 5	Act Comp Date 5 Ontime
2	1	NCAR-123	XXX	E	qqqq	7/25/12	8/1/12	7/27/12	Cancelled	8/14/12	CANCELLED	Cancelled			Cancelled
3	2	NCAR-456	XXX	M	wwww	8/21/12	8/28/12	8/28/12	Follow-Up	9/10/12	ESCALATED	Escalated 1			Escalated 1											.	Escalated 1
4	3	NCAR-789	XXX	M	eeee	9/5/12	9/12/12		Escalated 2	9/25/12	ESCALATED	Escalated 2			Follow-Up
5	4	NCAR-234	XXXX	M	rrrr	9/21/12	9/28/12	10/12/12	Late	10/11/12	11/29/2012	Late			Not Submitted									Follow-Up

<tbody>
</tbody>

NCAR​

 

[antexity]

HI There

Just wondering if anyone has any ideas or direction that I can use. I tried Advanced Filter and doesn't seem it will work for my requirements.

Thanks
Antexity