I am running windows-7 and using Excel 2007 Enterprise.
I am a truck driver and need to catalog my pay based on a changing day to day system. I am paid per mile, per stop, per hour, or per flat rate depending on the type of route.
I have made a workbook that uses =IF()
formulas to determine what pay scale to use and calculates how much I am to be payed automatically. As a result each workbook has 4 sheets, 1 sheet per trip as any more than 4 is so rare it may only happen once per year.
Sheet 1 pulls data from sheets 2, 3, and 4 automatically and fills in the correct cells and then recalculates my pay depending on what data is entered.
Now that I have a workbook for each day, as I can't combine days for several reasons not worth explaining here, I must have a total for each week to compare my gross earnings to. I have another workbook just for this reason, however I can't seem to get my weekly pay to pull data from my daily pay sheets as they are separate workbooks. I need my weekly pay to pull all total pay cells and total them on one sheet.
The cell in the daily pay is "B23 on Sheet-1", this is the location on all workbooks. I know this is a lot of information by I hope that it helps you understand what I need. So for what I need: I want to input the date that I am to be paid and have the workbook find the days needed to reference based on that date ie: 11/15/13 is pay for dates 11/03/13 – 11/08/13". now that it has the dates, find value in cell B23 on sheet-1 of all 7 dates and show it in this workbook. I can then have it compare my gross pay to what I should be paid if it is different, this part I already know how to do and already have it set up. Thank you in advance for your help.
Best Answer
Take a look at this and make sure you replace the
path
with the path on your computer where you keep your files.How it works: Place this in a module in a new workbook, put the payday in cell
A1
and run the macro calledTotal
. The dates and pay amounts will show up in columns B and C with the sum inC7
.