Access info between multiple workbooks using formulas, Please Help me.

[Kane1793]

I am running windows-7 and using Excel 2007 in Microsoft Enterprise.

I am a truck driver and I have not been able to get any help on this workbook problem. I have been over this before with someone else who thought he could help but couldn’t. I gathered all the information that he asked for and placed it here so no one has to ask 100 questions over 3 days, please help me. I need to catalog my pay based on a changing day to day system. I am paid differently based on the route/job I am doing: Per Mile, Per Stop, Per Hour, or Per Flat Rate depending on what I am asked to do. I have a screenshot of both workbooks on photo bucket so you can understand what I’m talking about better, the link is, Excel Daily Pay Workbook Photo by Kane1793 | Photobucket

I have made a workbook template that uses formulas mainly consisting of “<code>=IF()”</code> formulas to determine what pay scale to use, and then calculates how much I am to be paid automatically. All I have to do is enter the necessary numbers into the worksheets and presto, I have right in front of me how much money I made today, this much I have done with no problem. As a result each workbook has 4 sheets, 1 sheet per trip; I have named these worksheets as “Trip-1, Trip-2, Trip-3, and Trip-4.”

Trip-1 pulls data from Trips 2, 3, and 4 automatically and fills in the correct cells and then recalculates my pay depending on what data is entered. The “=If()” formulas are set up to test if data is on the next page and add it together to display on Trip-1. All is good so far, and works well.

Now that I have a workbook for each day, for daily turn in, 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 apply data to a cell 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 "O6” on “Trip-1", this is the location on all “Daily Pay” workbooks.

I know this is a lot of information, I hope that it helps you understand what I need. My template is saved as the date for each day I enter data, ie: 1-05-14. So Workbook 1-05-14 has all trips for that day calculated for how much I am owed and showing this total in cell “O6” on the first sheet-“Trip-1”.

On the workbook "Weekly Pay" I have cell “B3” blank, when I put a date (11-15-13) in "B3" cells "I4” – “I10" subtract 6 to 12 days (11/03/13 - 11/09/13) and displays dates in cells "I4” – “I10" for the previous week that I was paid for in cell "B3". I need a cell to see "I4" has a date(11/03/13), pull that "Daily Pay" workbook saved as "11-03-13" and display the "Total Pay" found in cell- “O6” from workbook-"11-03-13". Once I have a cell that is displaying this info from the cell I need I can make it work.

 

[iliace]

First of all, I would like to commend you on the detailed post regarding your problem.

There is no easy way to accomplish this. In the corporate environments that I've dealt with, given their requirements for an audit trail and such, the most common thing to do is to manually link the files.

To automate this task, you can use a VBA macro, as long as your file name, worksheets, and the cells remain consistent.

Another solution is to use an third-party software, such as morefunc, and utilize their custom worksheet functions. One such function is called INDIRECT.EXT, that allows you to create references to file names and access them in that fashion.

There is a number of options, especially given that you're an independent business owner not constrained by any corporate requirements on the add-in software that you use.