Trying to sum count from multiple worksheets

Hi,

You are making life complicated where it needn't be. 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 create a single sheet database that contains the following columns

Date
User Code *
Account Code *
Customer Name
Sales Rep *
Part Number *
Quantity


The * values 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.

When you say database do you literally mean Access or something within Excel?

It is a table of data in Excel.

Originally Posted by Jo Ross

The tricky thing is that the data capture is being entered by sales people. They are visual folk not practical folk so very visually orientated.
When you say database do you literally mean Access or something within Excel? Sorry if that is a really dumb question!

No, not Access or anything outside Excel. I simply meant a dedicated Excel sheet with the column labels I suggested. Typically I put those labels on say row 5 which wil be the top of the 'database' then above the data in say row 2 I add the same labels in row 2. The users enter data in Row 3. Once a new record has been added in row 3 I provide a button to click which adds a new first row to the database and copies the new record to that new row. This provides a simple way of building the database. Once you have that then it's a simple matter to analyse your data in just about any way you want with a Pivot Table - or if you want with formulae in other cells on other sheets.