New member - Analysing Several Worksheets and Producing Summary Report

Hi,

First post to this forum - hopefully someone can assist and it's not a too common question.

I have several worksheets in one workbook with identical layout on each sheet for shop sales.

There are 4 columns on each sheet with headers:

Column A = Item Code
Column B = Item Description
Column C = Price
Column D = Date Sold

The Date Sold column holds the date in format dd/mm/yyyy.

From each worksheet I want to be able to extract the current month's sales and output all data into one separate sheet for a summary report.

So if there are 10 worksheets I want to be able to produce one new sheet of all sales for current month (or any month) with all columns A,B,C and D output.

Hope that makes sense. I have used Excel quite a bit in previous jobs and should know how to do this.

All help grateful.

Many thanks.

Hi,

I came up with something, hopefully it's what you are seeking...

I added a helper column to each of the worksheets in Column F = Month, and added this formula to each row in the column: =Month(E row #). So the information looks like:

Item Code Item Description Price Date Sold Month
123 abc 5.55 10/12/2014 12
234 BCD 6.66 12/06/2014 6
345 CDE 7.77 13/07/2014 7
456 FGH 8.88 14/08/2014 8
567 IJK 9.99 15/09/2014 9
678 LMN 1.11 16/10/2014 10
789 OPQ 2.22 17/11/2014 11
890 RST 3.33 18/05/2014 5


The summary worksheet I setup as follows:

Month WS1 WS2 WS3 WS4 Total
1 0 0
2 0 0
3 0 0
4 0 0
5 3.33 3.33
6 6.66 6.66
7 7.77 7.77
8 8.88 8.88
9 9.99 9.99
10 1.11 1.11
11 2.22 2.22
12 5.55 5.55
Total 45.51 0 0 0 45.51

In the WS1 column I used the following formula: =SUMIF('WS1'!$E$2:$E$9, Sum!A2, 'WS1'!$C$2:$C$9).

I hope this helps.

Pam

Hi
Many thanks for this. Not had chance to try it yet, but appreciate the response.

Neil