Issue with summarising data through whole range

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.


A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception, doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

In your case I'm assuming that you will be adding many invoices and sheets if you carry on in the way you are. But just look at your invoices. I suspect many of the line items will not be used, and apart from the difficulty of summarising them you are wasting a lot of space.

I suggest you rethink your approach. Create a single database sheet which will hold, row by row all the information you are currently adding to individual sheets. You'll need columns for Invoice Date, Invoice Number, Contract Ref, Site, Order No, Item, Quantity, Rate & Total.

You can simplify data entry by maintaining lists of data for the line item descriptions and Units and then using these lists in cell data validation drop downs.

Once you have this database it will be simplicity itself to report on it any which way you want.

When you want to see a particular Invoice then it's just a question of designing an Invoice Report wherein you just enter an invoice number and then use data filtering to extract the details of that invoice.