Creating formulas between two sheets

Question

I have to create a couple of formulas that pull from an old sheet taken from the previous week, and a new sheet that was taken from the current day.

1. How can I calculate the new fields that were not in the old sheet?  I need to display how many new employees are in the new spreadsheet that weren't in the old sheet.  (Last name is column A, and First name is column B).

2.  How can I calculate which employees are no longer in the new spreadsheet?  I need to know how many users were deactivated since the old sheet was generated.

3.  How can I calculate which users had an Employee ID change (column C).  I will need to display the first and last names of the users that had changes made to their emloyee ID.

Any help would be greatly appreciated.  I'm not sure how or where to start with these formulas.

Answer 1

First, it depends on what you mean by "sheet".  Some people use Sheet as synonymous with Workbook, but they are two different things. If you actually have two workbooks, you will need to first, before starting these instructions, move one of the worksheets from one workbook into the other workbook, or change the references to include the workbook name (The easiest way to get inter-sheet and/or inter-workbook references correct is to select the other book/sheet and the range that you want when entering the formula using your mouse rather than typing it into the formula directly.)

First, insert three new columns in place of D in both worksheets, so that you have a blank D, E, and F.

In D2 of each (I have assumed you have a header row in row 1, and your data starts in row 2), enter the formula

=TRIM(A2 & " " & B2)

In E2 of each, enter a formula like this (what you enter in place of "OtherSheet" will depend on your actual sheets' names)

=MATCH(D2,OtherSheet!D:D,False)

In F2 of each, enter

=IF(INDEX(OtherSheet!C:C,E2)<>C2,"ID Change","")

and copy these three formulas down to match your data lists in each sheet.

Column D will contain the first and last name used to match the data. Column E will return an error if the employee in that row does not exist in the other sheet, and column F will return "ID Change" when names match but the IDs don't.