Do I need an advanced filter or something else?

[stugi]

Good afternoon,

I have a data sheet which lists projects as they are approved. The sheet contains, site reference numbers, project description, a project category, the value of the project and an approved date.
From this I have a summary sheet which uses a sumproduct formula to calculate the total value of approved projects by site for a financial year (using period start & end dates) and also a value for projects approved in the currnet period (referencing period start and end date).

I would like to be able to select the period value for a site and either filter or link to the data on the datasheet that makes up this amount, is that possible?

 

[Greg Truby]

(a) Are you familiar with pivot tables? They offer amazing summation and filtering tools (especially with Excel 2010). It sounds like their built-in functionality would closely match what you seek.
(b) Which version of Excel are you using?

 

[stugi]

Greg,

in reverse order

2010

I am familiar with them, never been a big fan to be honest but they may be down to my perception that they are somewhat limited.

What were you thinking? Replace my sumamry sheet with a pivot and use its functions to drill down?

 

[Greg Truby]

Yeah. Especially with 2010 and the new slicers. You can slice and dice data (hence their name) like there's no tomorrow. Plus if you want to see the details for a particular number, just double-click the cell that contains the summary figure that piques your interest and voila - all of the records that comprise that figure are copied to a new sheet for you to review.

 

[stugi]

Ok I'll take a look

 

[Cindinj]

Yikes - this has never worked right for me in any version. I always get ALL of the data returned when i drill down on one result in my PTs. Very frustrating. Any thoughts?

 

[Greg Truby]

...this has never worked right for me in any version. ...Any thoughts?

My thoughts would be that you just need a little more practice or there is some relationship between your data and your pivot that - at the moment - eludes your grasp. It is highly unlikely that out of the hundreds of millions of instances of Excel world-wide pivot table drilldown "has never worked right" on your one machine. I don't mean that to sound snarky or insulting. That's just "how the hog eats the cabbage"...

Remember, if you're dbl-clicking any kind of a row total or column total, you're going to get all of the component records that went into the total. I would take a second look at the results and try dbl-clicking various summary cells w/in the pivot and studying the resultant output from the dbl-click.

If it still looks like it's "not working right" then I'd say it's "time to play". Whenever I'm struggling to comprehend some aspect of Excel with production data, I usually find it helpful to close out of the production data and just use some random number generation and create a body of test data and start verifying my understanding of the tools and concepts.

Here are some formulas that are helpful in generating random data:

"Date"
=RANDBETWEEN(TODAY()-DATE(5,1,0),TODAY())

"Sales"
=ROUND(RAND()*10000,2)

"Units Sold"
=MAX(INT(B2/(ROUND(RAND()*500,2)+5)),1)

"Product"
=CHOOSE(RANDBETWEEN(1,4),"Gadget","Gizmo","Widget","Woozle")

"Sales Region"
=CHOOSE(RANDBETWEEN(1,8),"Kansas", "Nebraska", "Iowa", "Missouri", "Arkansas","Oklahoma","Colorado","Minnesota")