How to show data from other sheet depend on value??

[Malka]

Hi,


I have two sheets with the following data:
1/ List of projects - ProjectID and some other project level info
2. list of employees - each has some details including projectID, each
employee is linked to projectID

Now I have created a third page - At the top part (Second row), the
user select project ID, and with VLOOKUP - I show all project details.

My Question is::::
How can I show list of employees for each project????
That means: I would like to "Import" one sheet into another based on
criteria - How can I do it??

Many thanks,
Malka

 

[Max]

Here's one non-array formulas play to tinker with ..

A sample construct is available at:
http://cjoint.com/?cgmQrRenkC
Listing Multiple Employees By Project ID_Malka_wks.xls

In Sheet2,
Emp and Proj IDs are in cols A & B, data from row2 down

Emp Proj ID
Emp1 1111
Emp2 1112
Emp3 1111
Emp4 1112
Emp5 1112
etc

List the project ids in D23 : 1111, 1112

Then put in E2:
=IF($B2="","",IF($B2=INDEX($D:$D,COLUMN(B1)),ROW(),""))
Copy E2 to F2, fill down as far as required
(If there are 3 project ids listed in D24,
just copy E2 across an extra col to G2. And so on)

In Sheet3,
Project ids are listed in B2:C2 : 1111, 1112

Put in B2 (normal ENTER):
=IF(ISERROR(SMALL(OFFSET(Sheet2!$D:$D,,MATCH(B$1,Sheet2!$D:$D,0)-1),ROW(A1))
),"",INDEX(Sheet2!$A:$A,MATCH(SMALL(OFFSET(Sheet2!$D:$D,,MATCH(B$1,Sheet2!$D
:$D,0)-1),ROW(A1)),OFFSET(Sheet2!$D:$D,,MATCH(B$1,Sheet2!$D:$D,0)-1),0)))
Copy B2 to C2, fill down as far as required

The above will auto-extract and list all the employees under each project
id.
(You could then set-up your VLOOKUPs to place other data below the emp area)

 

[Malka]

Many thanks!!!!!!

 

[Max]

You're welcome !
Thanks for posting back ..