Need to extract specific data from a workbook with multiple worksheets

Good day,

I know the basic of formulas but what im asking assistance for I have no idea how to do.

In Monthly spreadsheet in A2 I want to type the employee number in, using this information I want the rest of the cells in row 2 to be filled with data linked to the employee. Employee data is found on multiple sheet in another workbook.

Monthly Data File:
Column:
A: EMPLOYEE NUMBER - I type in
B: INITIALS - Extract from Employee Worksheet (must have spaces if double initials)
C: FIRST NAME - Extract from Employee Worksheet (must not be in all in uppercase)
D: LAST NAME - Extract from Employee Worksheet (must not be in all in uppercase)
E: ID NUMBER
F: RACE - Extract from Employee Worksheet (must be typed in a specific way ie. "W" must be "W - White"; "I" must be "I - Indian"; "A" must be "A - African"
G: GENDER - Extract from Employee Worksheet (must be typed in a specific way ie. "M" must be "M - Male"; "F" must be "F - Female"
H: EE CATEGORY - formula based on cell J
I: DESIGNATION - Extract from Employee Worksheet (must not be in all in uppercase)
J: JOB GRADE - Extract from Employee Worksheet (single digits must have a "0" in front of digit ie, "7" must be "07")


The Employee workbook has 13 Sheets with multiple rows in them the column headings are: All text in this worksheet is in uppercase
A: CompanyNumber
B: EmployeeCode
C: Initials
D: FullNames
E: Surname
F: IDNumber
G:GroupCode
H: Gender
I: EmployeeStatus
J: DateEngaged
K: TerminationDate
L: PaypointCode
M: Paypoint
N: JobTitleCode
O: OccupationalLevy
P: JobGradeCode
Q: Job Title

The Links:
Monthly Data File /// Employee Data File
A: EMPLOYEE NUMBER - I type in /// Links to B: EmployeeCode
B: INITIALS /// C: Initials (Typed as DD need it to have a space - D D
C: FIRST NAME /// D: FullNames (All upper case only need first letter uppercase the rest lower case
D: LAST NAME /// E: Surname (All upper case only need first letter uppercase the rest lower case
E: ID NUMBER /// F: IDNumber
F: RACE /// G:GroupCode Typed as W;I;A;C need it to show A - African; C - Coloured; I - Indian; W - White
G: GENDER ///H: Gender Typed as M;F need it to show "M - Male"; "F - Female"
H: EE CATEGORY - formula based on cell J
I: DESIGNATION ///Q: Job Title (All upper case only need first letter uppercase the rest lower case
J: JOB GRADE ///P: JobGradeCode (single digits must have a "0" in front of digit ie, "7" must be "07")

I hope that you fully understand what I require based on the above explanation.

Regards

Question will both files be open when you update your sheet?
I suggest that you have both files open. start your formula for B2 "=vlookup(B2, (At this point highlight the columns B:Q in the other spreadsheet then type the rest of the formula),2,False)"
Make sure you fix the relatives in the formula : =Vlookup($B2,...!$B:$Q,2,False) - then copy it to where you need.

I suggest to read about PowerQuery (maybe PowerPivot also) then you can use your workbooks open or closed (doesn't matter) without any formula(s) or only a few of them.

PowerQuery 1
PowerQuery 2

Originally Posted by CRIMEDOG

Question will both files be open when you update your sheet?
I suggest that you have both files open. start your formula for B2 "=vlookup(B2, (At this point highlight the columns B:Q in the other spreadsheet then type the rest of the formula),2,False)"
Make sure you fix the relatives in the formula : =Vlookup($B2,...!$B:$Q,2,False) - then copy it to where you need.

thanks for the Suggestion but this Vlookup formula only refers to one sheet, I need it to search all 13 sheets within the workbook