Creating a "Summary" Table

Hello and welcome to the forum.

This is very similar to another post I've just answered. 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 or which at first glance seems the best way of capturing data, 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.

So before you get too far with this I'd seriousl consider creating a single sheet database that contains the column labels you show in A9:H9, plus additional columns for the labels in C1:C6 and a column for the Item number (i.e. the current sheet names). We could write a simple macro to help you do that if you are prepared to change your way of collecting data.

AFter doing that the answer to your requirement would be to use Excel's Filtering functionality


Many of the values you capture could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

Let me know if you are happy to proceed as above.

OK then,

Upload a copy of the workbook with say three sheets for me to work with as a proving ground and I'll put together a generalised macro that will consolidate all sheets.