Moving cells within a formula (forumula includes cells from several tabs within 1 workbook

[Texas123]

I have an interesting challenge for the excel guru out there. I have a workbook that has a tab for Quarter-to-date employee data and then seperate tabs after that for Month-to-date data for employees. The Quarter-to-date tab has two formulas per employee that pulls data from each of the Month-th-date tabs. If we never added or removed any employees from any of the tabs this would be fine. However, when we add an employee, for example, in alphabetical order that requires us to insert row(s) which moves the cells that are linked to formulas. For example, Jack Doe's formula in the QTD "Billables" cell is the following: [=Dec.12!C9+Nov.12!C9+Oct.12!C9]. If we add employee in the row right above him in December that pushes his cells down to C10 instead of C9 and thus throws the formula off. How can we link all cells connected with a certain person's name to a formula in another tab of the same workbook? I have attached the sample woExcel Example.xlsxrkbook I'm referring to

[Andrew-R]

In your blank cell in D8 I put the following formula (and then set the text colour to white so that it couldn't be seen)

=MAX(IF(Dec.12!$A$8:$A$10 & " " & Dec.12!$B$8:$B$10 & " QTD"=A6,ROW($A$8:$A$10)))

This is an array formula, so must be entered with Ctrl-Shift-Enter, not just enter.

Then in E8 I went with:

=INDEX(Dec.12!C:C,$D8)+INDEX(Nov.12!C:C,$D8)+INDEX(Oct.12!C:C,$D8)

Dragged across to F8, and then cells D8:F8 could be copied down to the next employees.

[oeldere]

VBA solution.

consolidate all data from all sheets on on summary sheet and after that made an pivot table of it.

see the attached file.