Search sheet for name in row and then copy cell contents where name exists in all sheets

[mb77]

Hi, i have a workbook for mobile phone bills. Each month a new sheet is created, with one row per staff member. The first month worksheet has the employees department along with call costs and number of call etc, subsequent sheets are missing the department (Cost Centre Code1) - please see inline example for column headings..
What function can i use to read each staff members department from the first worksheet and then populate the subsequent worksheets with that missing call information if their mobile number is found?
Untitled1.xlsx
If you assume that first sheet with departmental data is Sheet1 and subsequent months are Sheet2 Sheet3 etc.

[Glenn Kennedy]

How are the data on sheets 2 and 3, etc, organised? You've left us guessing....

[JeteMc]

In the following file find that the employee's name and department are both populated to sheet2 when the employee's phone number is entered.

Copy of Untitled1.xlsx

Hope this helps.

[Richard Buttrey]

Before proceeding along the lines you request can I ask why you want to add subsequent months data in separate tabs? Almost certainly this is not an optimum way to proceed. You'll be better served by adding each month's data underneath the preceding months and creating an additional column to hold a date for each record - which could be say the 1st of each month.

Then all you need is a second sheet that holds a table of phone numbers in one column and their associated cost centre codes in another. Then each month new data is added you just need to add a regular VLOOKUP() function to the Cost centre code column for the new month's data - or better still have a simple macro to do it.

Any subsequent analysis will be simplified if you adopt this approach.

[mb77]

Thanks for your help - i will add another sheet with phone number and costs centre and then complete a vlookup