If i entered any project code in sheet2 display all data of that project

**koolguys4u** · 06-20-2009, 04:25 AM

Hi everyone,

i have my employees data in sheet1.
If i entered any project code in sheet2
column b1 it will diplay all the employees
details along with emp code and name in next rows

sample data file attached

**DonkeyOte** · 06-20-2009, 04:41 AM

Use a Pivot Table, set:

PAGE Field: Project Code
ROW Field 1: Name
ROW Field 2: EmpCode

(no need to populate Data Field but if you wanted you could add Name to that Field, it will default to COUNT)

You can then filter the results of Name & EmpCode by altering the selection made in the PAGE Field (Project Code)

**JBeaucaire** · 06-20-2009, 04:41 AM

Hi KoolGuy. This technique uses an array function, so any changes you make to the formula in column A, be sure to confirm them by pressing CTRL-SHIFT-ENTER. Once the first value appears properly, then you can copy the formula down a ways.

In A3:

=IF(ROW()-2>$C$1,"",INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!$C$1:$C$100=$B$1,ROW($A$1:$A$100),""),ROW()-2)))

**koolguys4u** · 06-20-2009, 05:13 AM

Thanks for your prompt reply,

One more thing i forgot to mention that If i entered the Wrong Project Code or Invalid Project code it will give the messg that Invalid Project Code ...

Thanks once again for prompt reply to both of you....

**JBeaucaire** · 06-20-2009, 09:24 AM

Put this replacement formula in C1:
=IF(COUNTIF(Sheet1!C:C,B1)=0,"Wrong Code",COUNTIF(Sheet1!C:C,B1))

And this replacement array formula in A3:
=IF(OR($C$1="wrong code",ROW()-2>$C$1),"",INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!$C$1:$C$100=$B$1,ROW($A$1:$A$100),""),ROW()-2)))