Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data
I am not sure if this question belongs to this discussion group but here it is
I have one sheet that contains all employee information I would like to be able to type any employee ID in a seperate sheet in the same workbook an pull all the related information listed in sheet 1 thanks ce |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data
Try using VLookUp function please
"Curtis" wrote in message ... I am not sure if this question belongs to this discussion group but here it is I have one sheet that contains all employee information I would like to be able to type any employee ID in a seperate sheet in the same workbook an pull all the related information listed in sheet 1 thanks ce |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data
Thanks
I was hoping I would not have to do a VLOOKUP in all columns and rows. "PY & Associates" wrote: Try using VLookUp function please "Curtis" wrote in message ... I am not sure if this question belongs to this discussion group but here it is I have one sheet that contains all employee information I would like to be able to type any employee ID in a seperate sheet in the same workbook an pull all the related information listed in sheet 1 thanks ce |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data
We could use either VLOOKUP or INDEX / MATCH (which is generally more
versatile than VLOOKUP) Assume source data in Sheet1, cols A to E, data from row2 down. Col A = Emp ids (I'll presume that the Emp ids are text numbers in 6 digits format, possibly with leading zeros - as typical of data taken from HR host sys), with cols B to E containing associated data In Sheet2, Let's assume you'd be entering the emp ids as numbers in A2 down Using VLOOKUP Put in B2: =IF(ISNA(MATCH(TEXT($A2,"000000"),Sheet1!$A:$A,0)) ,"",VLOOKUP(TEXT($A2,"000000"),Sheet1!$A:$E,COLUMN (A1)+1,0)) Copy B2 to E2, fill down as far as required. Cols B to E will return the required results from Sheet1. Alternatively, using INDEX / MATCH (you could try this in another sheet) .. Put in B2: =IF(ISNA(MATCH(TEXT($A2,"000000"),Sheet1!$A:$A,0)) ,"",INDEX(Sheet1!B:B,MATCH(TEXT($A2,"000000"),Shee t1!$A:$A,0))) Copy B2 to E2, fill down as far as required. Cols B to E will return the required results from Sheet1. For more info, try Debra's nice coverage on VLOOKUP or INDEX/MATCH at her: http://www.contextures.com/xlFunctions02.html VLOOKUP http://www.contextures.com/xlFunctions03.html INDEX/MATCH There's also some sample workbooks available for d/l & study -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Curtis" wrote: I am not sure if this question belongs to this discussion group but here it is I have one sheet that contains all employee information I would like to be able to type any employee ID in a seperate sheet in the same workbook an pull all the related information listed in sheet 1 thanks ce |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling data
"Curtis" wrote
I was hoping I would not have to do a VLOOKUP in all columns and rows. See also my response in the other branch, where I've provided an example using both VLOOKUP and INDEX/MATCH. We could always use an incrementer (eg: COLUMN(A1)+1) for the column index part in the VLOOKUP. Then we could just copy the formula straight across as far as required to pull in all the columns from the source sheet. Another alternative is to use INDEX / MATCH with the INDEX part of it (ie the col to be returned) left relative, so that it increments when we copy across. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling out data based on font colour | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |