Help with formula pulling names from one tab to another

%3CLINGO-SUB%20id%3D%22lingo-sub-2143569%22%20slang%3D%22en-US%22%3EHelp%20with%20formula%20pulling%20names%20from%20one%20tab%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143569%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20pull%20a%20list%20of%20employees%20from%20one%20tab%20to%20another%20based%20on%20their%20job%20function's%20unique%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20three%20jobcodes%20in%20this%20department%20that%20are%20housed%20in%20one%20list%20in%20one%20tab%20all%20mixed%20up%20together%20that%20I%20would%20like%20to%20pull%20into%20a%20different%20tab%2C%20sorted%20into%20the%20three%20groups.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3EWorkbook%20info%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOriginal%20List%20Information%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECol.%20A%20-%20Jobcode%3C%2FP%3E%3CP%3ECol.%20B%20-%20Employee%20Name%3C%2FP%3E%3CP%3ECol.%20C%20-%20Hire%20Date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDestination%20Tab%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECol.%20P%20-%20Jobcode%20(hardcoded%20in%20so%20I%20would%20separate%20the%20jobcodes)%3C%2FP%3E%3CP%3ECol.%20Q%20-%20Employee%20Name%20(Desired%20Formula)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3EI%20have%20already%20tried%3A%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3DINDEX(OriginalList!B%3AB%2C1%2BMATCH(Destination%20Tab!P5%2COriginalList!A%3AA)%2C1)%3CUL%3E%3CLI%3EThis%20works%20pretty%20well%2C%20but%20the%20%221%2B%22%20doesn't%20automatically%20increase%20to%20%222%2B%22%20when%20I%20drag%20it%20down.%20Ideally%20I%20would%20like%20this%20formula%20to%20automate%20as%20much%20as%20possible%2C%20but%20if%20nothing%20else%20works%20I%20can%20go%20in%20and%20individually%20change%20each%20formula%20so%20it%20picks%20up%20the%20next%20name%20in%20the%20list%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3E%3DIF(Vacation!A%3AA%3D'F''2X%20Available%20Hours%20(2)'!P6%2CINDEX(Vacation!B%3AB%2CMATCH(P6%2CVacation!A3)%2C1)%2C%22%22)%3CUL%3E%3CLI%3EThis%20creates%20a%20%23SPILL!%20error.%20I%20tried%20futzing%20with%20it%20but%20I%20just%20can't%20make%20it%20work%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3E%3DFILTER(Vacation!%24B%242%3A%24B%2411%2CVacation!A2%3AA11%3D'F''2X%20Available%20Hours%20(2)'!P7%2C%22%22)%3CUL%3E%3CLI%3EThis%20pulls%20the%20first%20employee%20name%2C%20but%20won't%20pull%20the%20the%20next%20in%20the%20list%20when%20I%20drag%20the%20formula%20down%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20any%20help%20you%20can%20give%2C%20this%20has%20been%20really%20bugging%20me%20the%20past%20few%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2143569%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143644%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%20pulling%20names%20from%20one%20tab%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969750%22%20target%3D%22_blank%22%3E%40e_anderson023%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20much%20easier%20if%20you%20attach%20some%20sample%20data%20so%20we%20can%20understand%20what%20you%20exactly%20require.%20I%20appreciate%20that%20this%20may%20be%20sensitive%20data%20but%20if%20you%20create%20a%20few%20lines%20with%20false%20names%20and%20removed%20the%20data%20from%20the%20other%20columns%20(apart%20from%20the%20headings)%20that%20this%20would%20help%20greatly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%20-%20what%20do%20you%20mean%20by%20your%20comment%20%22%3CSPAN%3E(hardcoded%20in%20so%20I%20would%20separate%20the%20jobcodes)%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Emany%20thanks%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPeter%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

EDIT: Added example book.

 

Hi All,

 

I'm trying to pull a list of employees from one tab to another based on their job function's unique code.

 

There are three jobcodes in this department that are housed in one list in one tab all mixed up together that I would like to pull into a different tab, sorted into the three groups.

 

Workbook info

 

Original List Information:

Col. A - Jobcode

Col. B - Employee Name

Col. C - Hire Date

 

Destination Tab:

Col. P - Jobcode (hardcoded in so I would separate the jobcodes)

Col. Q - Employee Name (Desired Formula)

 

 

I have already tried:

 

  • =INDEX(OriginalList!B:B,1+MATCH(Destination Tab!P5,OriginalList!A:A),1)
    • This works pretty well, but the "1+" doesn't automatically increase to "2+" when I drag it down. Ideally I would like this formula to automate as much as possible, but if nothing else works I can go in and individually change each formula so it picks up the next name in the list
  • =IF(Vacation!A:A='F''2X Available Hours (2)'!P6,INDEX(Vacation!B:B,MATCH(P6,Vacation!A3),1),"")
    • This creates a #SPILL! error. I tried futzing with it but I just can't make it work
  • =FILTER(Vacation!$B$2:$B$11,Vacation!A2:A11='F''2X Available Hours (2)'!P7,"")
    • This pulls the first employee name, but won't pull the the next in the list when I drag the formula down

 

I appreciate any help you can give, this has been really bugging me the past few days.

 

Thanks!

2 Replies

@e_anderson023 

 

Hi 

 

It would be much easier if you attach some sample data so we can understand what you exactly require. I appreciate that this may be sensitive data but if you create a few lines with false names and removed the data from the other columns (apart from the headings) that this would help greatly.

 

BTW - what do you mean by your comment "(hardcoded in so I would separate the jobcodes)"

 

many thanks

 

Peter

 

@peteryac60 So sorry about that, I have added an example file to my post. 

 

What I meant by "hardcoded" is that I would type the desired jobcode in and it was not being pulled from another area in the workbook.

 

Please let me know if any more clarification would be helpful.

 

Thank you!!