Excel formula for report

[Rob]

I have one worksheet with multiple tabs for each pay period (we get paid
every 2 weeks). Each tab has an employee name, how many hours they worked,
how much they got paid and the total they were paid.

What I want to do is make a "report" page which would go through each of the
tabs and sum all of the hours worked for a specific employee, add up all of
the money they were paid from each of the tabs.

How do I do that?

 

[Rob]

To be a bit more specific

Tab 1
Name hours worked amount earned
Emp1 5 $45
Emp2 3 $18
Emp3 10 $100

Tab 2
Name hours worked amount earned
Emp1 7 $56
Emp2 8 $48
Emp3 5 $50

Tab 3- Report
Name hours worked amount earned
Emp1 =total from tab1&tab2 " "
Emp2 " " " "
Emp3 " " " "


Thanks for any help

 

[RagDyer]

If each sheet has the same information in the same cell on each sheet, this
can be accomplished relatively easily.

Say the weekly total is in J6 on *each* sheet.

You can create a situation, where this formula will calculate your total
from all sheets:

=Sum(Start:End!J6)

To do this, enter a "Dummy" blank sheet to the immediate left of your first
pay period tab, and another to the right of your last tab.

Name the first
Start
and the second
End

Place your summary sheet *outside* of this "sandwich".

Place any new pay period sheets you create *inside* the sandwich.
Tabs *physically* between the Start and End tabs will be totaled by the
formula.

Without changing your formula, you can move sheets outside of the sandwich
to see "What If" scenarios.

 

[ROinBOA]

Rob....several suggestions:
First - You really shouldn't create a separate tab for each pay
period....your workbook will become unmanageable very quickly. Add the time
period to each line of data and store it all in one sheet.
Second - There are a number of way to analyze the data and produce
summarized reports. Inserting Sub-totals into your data will display it on
the tab. But the real tool here is Pivot Tables and Pivot Reports. Prett
complicated to describe here but not really that difficult to learn. If the
help files aren't sufficient there are a number of books available that will
help. I'm NOT and expert in Excel but I picked up a book a few years ago and
learned about Pivots and it changed my (Excel) life!

 

[Conan Kelly]

Rob,

I have one worksheet with multiple tabs

Do you have one worksheet with multiple tabs, or do you have one
workbook/file with multiple worksheets? Workbooks/files can have multiple
worksheets, each worksheet has only one tab. The worksheet's tab displays
the name of the worksheet and can be colored.

I know this is a really computer-nerdy, anal-retentive thing to say. Feel
free to imagine me saying this in a nasally, pinched voice with coke-bottle
glasses on...."Well AAAC-TUALLY, the correct, technically-precise way
is...yada-yada-yada!!!"

If I could, I would try to teach everyone the correct terminology and make
sure they say "worksheets" instead of "tabs" and "workbooks" (or "files")
instead of "worksheets".

If you continue to say "worksheets & tabs" instead of "workbooks &
worksheets", I won't hold it against you. There are a lot of people who say
the exact same thing. Most of the time we can figure out what people mean.
I can't speak for everyone who (<--or is it whom?...I can never remember!!!)
helps out here, but I know I would appreciate it very much if you could
change your thinking to "workbooks & worksheets". I imagine that most
people here would appreciate it as well.

HTH,

Conan