Excel - Time Sheet Formula

The problem i have is the formulas i have used in the template are not quite what i'm after. The formula in column 'O' should be to make sure that a minimum of 8 hours have been in column 'M' and that will mean they will get paid 'D'. Anything over that as per column 'N' will be multiplied by there overtime rate in column 'E' and added to column 'O'. at the moment before any hours are entered it shows there daily rate which i dont want.
Also i would like a rolling total to show in the "Summary" sheet from all the new sheets that wil be created from the template code. At the moment i have it set up for one sheet but i dont know how to include for sheets that are not created yet...... Please advise
Weekly-Pay-Schedule.xls
CarlandrewlewisAsked:
Who is Participating?
 
Curt LindstromConnect With a Mentor Commented:
I can do it like this. See attachment.

To do it when you add a new sheet gets a bit too complicated since you would not only have to work out how many week sheets there are, but also try to construct formulas in the summary sheet based on the sheet names. This is easier and works just as well. Just press the button to get the current total. The macro used by the button is in module1.

Curt
Mod2-Weekly-Pay-Schedule.xls
0
 
Curt LindstromCommented:
I think this is what you want for your formulas. See attachment.

For your rolling summary you can just keep adding the total from each sheet ='Week 1'!O124+


Curt
Mod1-Weekly-Pay-Schedule.xls
0
 
CarlandrewlewisAuthor Commented:
The formulas are perfect now thank you. It's now just the rolling total.  What i have is when you enter a name in the "New Sheet" it will automatically create a new worksheet called the same. What i need if possible is when it creates a new sheet it adds the total aswell.......
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CarlandrewlewisAuthor Commented:
The toatls are to show in the summary sheet, i think this makes sense!!
0
 
CarlandrewlewisAuthor Commented:
Thanks for the advice, it all works perfect now....

Thanks for your time
0
 
CarlandrewlewisAuthor Commented:
Curt, you help me with the above problem where you created a macro to calculate the totals of all created sheets. I am going to post a question in regards to 'Excel Macros - Calculating Totals' could you have a look at it for me.

Regards

Carl
0
 
Curt LindstromCommented:
Sure Carl, I'll look at it unless someone else beats me to it!

Curt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.