Daily profit / loss oil trucking

Hi

I think the best thing is to include all of the payment information on the Ticket Sheet by adding some columns to look up the values as shown on Sheet 1 and multiply by the number of barrels.

You can then build all of your pivot tables over a single set of data. This will give you the flexibility to develop this further at a later date.

I'd also convert your data range on the Ticket Sheet to a table so that it will easily grow and make your pivot tables easier to keep updated.

I'll try to put together a sample for you just to show what I mean.

I've knocked up a quick update to your workbook with a couple of new tabs

The New Ticket Sheet has a table - I just copied the basic data from your original. I've added new columns which calculate the /bbl price based on the Oil Well, Destination and Trucking Company as per your details on Sheet 1. Then I've multiplied these by the number of barrels, and calculated the profit.

The formulas in the per barrel columns look like this:

=INDEX(Sheet1!$E$2:$E$13,MATCH(1,(Sheet1!$B$2:$B$13=[@[Oil Well Name]])*(Sheet1!$D$2:$D$13=[@Destination])*(Sheet1!$C$2:$C$13=[@[Trucking Company]]),0))

Note that these are ARRAY formulas so if you change anything, remember to press Ctrl-Shift-Enter (this will put in the curly brackets)

I've created a simple pivot table on the New Pivot tab - you can just play around with the format to fit what you need.

Hope that's enough to get you started anyway. If you get stuck just holler

My pleasure

Don't forget to mark your thread as "solved" - you can always come back and ask more questions later if you need to

Go to the top of the thread - you will see a Thread Tools option. You can mark it as SOLVED there.