I think I need array formula… Can you help?

Sheet 1 is a table of orders placed, with column showing PO, supplier etc.

On sheet 2 I want to have a summary, which will show all orders placed on a chosen cost centre, or with a certain supplier for example.

Advanced filter copies the data across, but doesn’t update for new data added on Sheet 1.

Vlookup will struggle with inserted rows etc.

Early on in my google search I found an example with array formula, but I can’t find it again now…

pivot table?

That’s where I started… can I get it to actually return the data, and not just sums, counts, averages etc?

http://singletrackworld.com/forum/topic/just-learnt-a-powerful-new-excel-technique

Sumproduct with the double unary will probably do it.

I posted on it here a long time ago. Out and about at the moment but maybe able to help later

Hmmm, have found how to drill down in to data from Pivot tables… will have a play

I can drill down in to the data from the pivot table, but that is just a copy of the original – it doesn’t update.

You need to define a dynamic range assuming the issue with not updating is that it doesn’t add new data to your set. (If not just right click and refresh, but i assume you knew that)

Its done through (i think, sorry on mobile) data tab and creating a named range. Then nested count and offset function to set the bottom of your data a varriable distance from the top. Google is your friend.

Edit here

Right click on the pivot & refresh, then re-drill-down.

Can be handled with a simple macro if you’re doing this regularly.

Edit: You’ll also need to ensure the data range is being picked up by the pivot – either full columns (pet hate) or dynamic range

Agree, I can do the drill down bit again after a refresh. It just seems a bit clunky and not ideal if I want the table output displayed within a report type of page layout with headings etc.

You don’t need a Named Range for pivots to pick up all data when new data is added. Just ‘Format As Table’ and then ensure all newly added data is part of the table

do you have Automatic calculation including tables turned on (IIRC there’s an option that doesnt automatically update tables but will do other calcs)

It didn’t actually take me 5 years to renember the answer to Stoner’s issue on that old thread linked to above – I was just knocking-up a training document and it occurred to me that what I couldn’t recall back then is something I use on regular basis now.

The issue:

It appears that INDIRECT wont work with Dynamic Ranges.

The answer: http://dailydoseofexcel.com/archives/2005/03/01/indirect-and-dynamic-ranges/

I use a re-jigged version of Option #2 and very nicely-nice does it work.