Auto sort / fill ?

I use excel to summarize weekly loads hauled by our trucks. I use the first sheet to enter all data for all loads. Then filter and copy and paste to additional sheets in order to create smaller summaries for different drivers, crews, landowners. This works okay but there must be a way to auto populate the additional sheets from the first sheet. For Example:

First sheet "Load Summary" has all load information entered.

load summary.jpg

I then need to make a separate summary with just loads that Mark hauled, on sheet "Mark". The bottom of this sheet is set up to auto calculate what to pay Mark based on what/where he hauled.

Mark Loads.jpg

I'm thinking there must be a formula to copy the row if column K matches "Mark" but I'm unsure of what it is. Depending on the week there are sometimes 150-200 loads on the "load summary" sheet. Thanks in advance.

Originally Posted by Neil_

Seems a lot of unnecessary extra work. why not add a filter to the Load Summary and a subtotal to the bottom of column I?

=SUBTOTAL(9,I6:I9999)

We give the drivers/crews/landowners a summary containing each load with their payment check to ensure all loads are paid for. One company we haul for requires this format (date, lot, lot # etc etc etc) so we have standardized all of our summaries with this format. This example doesn't show it but some loads are measured in Board Feet instead of tons, and different species and mills pay differently so a simple subtotal unfortunately won't work.

For Example: This driver is paid depending on where the load is hauled

mark 2.jpg

This crew is paid by species, and also has deductions taken each week.

nicols.jpg

I do currently use a filter, then copy and paste the filtered data to its respective sheet. Just thinking there's got to be an easier way.

Originally Posted by Neil_

I would make the master page a table. This helps with consistency of data on the other sheets. I would then create pivot tables for each type of summary on the other sheets. Then all you have to do is refresh them when more data is added. If you could post a small sample, I could set a couple up as examples.

Here is an example book. The first sheet is filled. The following sheets are blank templates like I showed before. If you could set a couple up that would be great. Thanks!

Load Summary example.xlsx

I will check it out thanks!

Originally Posted by Neil_

HI,
I've added 3 pivot tables to show what can be done. I've added one for Mark (filtered on driver) with as much detail as I can. I've added one for Nichols (filtered on Crew), again with full detail. The third I've added on Jake (filtered on Lot), this time with Species as column headers. I also added a calculated field for Price (not sure if this differs dependent on client). You can remove details by un-ticking them on the field lists. Or add more.

i added a calculated field for price (not sure if this is the same for all clients)

I've filtered out mills with no data on the Nichols summary.

To refresh the data, delete the rows from the load summary table and paste values into it when you have new data. Right click the pivot tables and select refresh.