Extracting data related to a single month from a whole range of data

**Excel_Monkey** · 05-29-2012, 04:03 AM

I have a problem I have found to avoid solving for ages now, but I think the time has come.

I have a sheet called input which will have data added to it on a daily basis.

At the end of each month I have to submit a report which shows the data relating to that month. I also have to show a rolling total (all previous data + new month).

Current - NOT ideal solution

I have to set up about 2 years of separate work sheets and manually change all of the formulas to read the appropriate data from the input sheet. At the end of the 2 years I then have to create more sheets. Also I have to copy the formulas down the input sheet to around row 10,000 as the could be 30 entries a day. This makes the workbook farily large even when it isn't even populated with any useful data.

Is there a more elegant solution to all this?

I have attached an example fil, I have had to strip a lot of data out and there are only 2 monthly sheets shown otherwise the file exceed the upload limit.

I would be more than happy if the sheet only generated THE currents months summary sheet plus the running total.

any help much appreciated.

**Richard Buttrey** · 05-29-2012, 04:16 AM

Hi,

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.

So:
1. Combine all the data from your monthly sheets into one sheet in a simple two dimensional contiguous table.
2. Add an additional (say first) column which contains the month start date.
3. Make sure that there are no column or row gaps.
4. Place meaningful column labels at the top of the data. Create a dynamic range name that will

Now you'll find that you can use a Pivot table to summarise (and analyse your data). In addition since you now have a 'proper' database you can use standard data filter/advanced filter to view or extract records for reporting.

**TMS** · 05-29-2012, 04:21 AM

This looks impractical and unmanageable ... and certainly not scalable. You seem to be mixing raw data (input) with calculations and reporting (charts) and the whole effect is, at least to me, confusing. I don't think trying to present raw data, calculations and charts in this way ever works well ... especially with huge volumes of data.

It's one of those situations where you might say: If I wanted to go there, I wouldn't start from here".

My suggestion would be that you have one sheet that is used for ongoing raw data input ... no calculations, just the numbers. On another sheet, you could have (summary) calculations, COUNTIFS, SUMIFS, AVERAGEIFS, whatever; and on another, a chart or charts mapping the summary calculations.

Alternatively, and perhaps better, a Pivot Table based on the raw data or the summary calculations with chart(s) reflecting the Pivot Table data.

Just my thoughts ... can't really fix what you've got.

Regards, TMS

**Excel_Monkey** · 05-29-2012, 05:04 AM

Thanks, I think I will leave this thread open if you don't mind?

I will attempt to take on board this advice, but it is going to involve me learning what a pivot table is; how to create a dynamic range amongst other problems.

**TMS** · 05-29-2012, 05:23 AM

A dynamic range ...

Let's assume that you have only raw data in the sheet and column A is the key field ... in this case, the input dates. So, row 1 will have a header, rows 2 onwards will have dates (with no spaces) and the rest of the columns have values relating to the input dates.

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

This formula defines a range. The first part is static and is the first cell in the range: Sheet1!$A$2 ... column 1, row 2

The second (end) cell in the range is calculated using INDEX. COUNTA((Sheet1!$A:$A) determines how many non-blank cells there are in the range, hence, no spaces allowed. This count is used to INDEX column A and give you the last cell in the range ... and dynamically changes to reflect the number of entries in column A.

INDEX is non-volatile so will not affect the performance of the sheet.

Regards, TMS

**Excel_Monkey** · 05-31-2012, 09:26 AM

Ok I have redesigned my spread sheet trying to take on board some of the advice above.

I now a sheet called INPUT which contains raw unprocessed data only.

There are 2 sheets of pivot tables D and W (I have never used pivot tables before), which pull data from the INPUT sheet. I don't think I am making best use of the pivot table function here but it appears to give me what I want.

I then have a MONTHLY sheet. In this sheet I want the user to enter the month that they wish to report on in to cell A1. I then want a formula to pull the data from that month only and populate column G.

I have been using the following formula (see MONTHLY, G5), but it is incorrect:

=SUMIF(D!$A:$A,$A$1,D!$B:$B)

How can I call up only data from a particular month?

My second problem is illustrated in sheet CUMMALITIVE. Cell G 18 reurns #REF!, because there was no data with necessary code on the input sheet, therefore and column does not existed on the D pivot table sheet for code CA items.

I have attached the spread sheet with comments.

I am please I have discovered pivot tables finally but really cannot work out how to achieve the reports I need.

Any help is very much appreciated.