Collection Lists & Summaries

[jonsi]

I have a list of collections made, each with different dates and different amounts all input into Sheet1.
Four Columns with the following headings: Date : Quantity : Ref : WeekNum :
02/02/13 : 250 : abc1 : 5 :
03/02/13 : 180 : abc2 : 5 :
09/02/13 : 210 : xyz1 : 6 :
17/02/13 : 387 : xyz2 : 7 :

and so on ...there will be around 500 records, each on it's own row

Whilst I can AutoFilter these to show only All WeekNum 5 entries for example, I want to be able to pull these over onto Sheet2 by means of formula based on an input cell on Sheet2 to create a weekly Summary Print. I Know I can summarise using SUMIF and COUNTIF etc. but I need to see each entry individually on the summary print (to ensure that we are tracking and including all collections) Each summary is backed by physical paper copies and has to stand up to a physical audit.
I would prefer to have a (sort of) Template on Sheet2 into which the data is returned rather than use Filter or SubTotal on Sheet1.

The number of entries for any given week is variable ...6 one week, 11 the next and so on.

Is this do-able without anything overcomplicated being involved?

thanks
Jonsi

 

[xlbo]

Best bet is to use MS Query internally on the workbook. you can set it to work to pick up a criteria to apply to teh data range e.g. weeknum = 5. that way you keep your sheet1 data in tact and only grab what you need onto sheet2 for reporting

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jonsi]

Thanks Geoff

Think I may have worked a solution using a Pivot Table and putting SUM Totals above it rather than below (because the range down is variable). All I do is overstate the SUM Range (C10:C600) returning the result above the Table. The table gives me the listing I want line by line so it all (kind of) makes sense.

All I need to do now is wrap the 'pretty bits' of the report around this core data.

Thanks for your quick return of advice though.
Jonsi

 

[macropod]

For a different approach to matching in Excel with repeated data, see:

http://windowssecrets.com/forums/sh...es-from-a-list?p=734296&viewfull=1#post734296

http://www.techsupportforum.com/forums/f57/excel-formula-issues-375768.html#post2567119

Cheers
Paul Edstein
[MS MVP - Word]

 

[xlbo]

Hi Jonsi - to remove the need for overstating formula ranges, have a look at dynamic named ranges

Lets say your pivot table is on Sheet2 and starts with a page field in A3

Let us also say you want to sum columns B, C & D

you can create 3 dynamic names:

dr_ColB
=offset(sheet2!B7,,,counta(sheet1!A:A)-2,1)

dr_ColC
=offset(sheet2!C7,,,counta(sheet1!A:A)-2,1)

dr_ColB
=offset(sheet2!D7,,,counta(sheet1!A:A)-2,1)

The -2 is to remove the count for the page field and the row field name to give you the count of cells populated in column A - this will be what defines your range

You can then simply use =sum(dr_ColB) for your sum and not have to worry about extra data causing the sum ranges to be too short

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question