Is this a solution for sumproduct?

[SilverSN95]

Hello,
I need to break down a large table of data by different categories for a report, a lot like what a pivot table will do. However, I was problems getting the pivot table to behave like I needed it to and I've decided to do most of the work with a macro.
What I need are totals based on conditions in other columns.
For example, I have columns : Region, Status, Type.
As an example, in the report I want to have the macro total another column x for all entries Region = Atlanta, Status = New, and type = Renewal. Later I will place this value into a cell.

Based on my readings here it seems like sumproduct could achieve this, but as I've looked around I've seen other functions and lookups that seem like they can also do what I want.Could you give me some advice on the best way to do this?

Thanks.

[Bob Phillips]

Why not use Advanced Filter with an event macro to set the filter on change of one of the criteria.

[SilverSN95]

Xld, The user will only be changing one criteria manually, in this case "Region". The report will then display totals broken down further by type, status, and a few other fields. The actual data is on a different worksheet so making changes to the filter there wont be useful.
I could set the filter in the macro sequentially, getting the total I need from the filter and then resetting it with different criteria for the next but I don't think that is what you meant.

[Bob Phillips]

A look at the workbook might help.

[Paul_Hossler]

However, I was problems getting the pivot table to behave like I needed it to

Might be worth re-visiting the PT approach, since what you described sound a lot like a PT would be the way to go

Maybe post a small WB and what the problem was you were having with the PT ????

Paul

[SilverSN95]

Ill get back to you with a sample WB, I'll need to trim down the one I have. My major concern with the pivot table is I need to be able to reliably paste in new data on the page the pivot is feeding from and have it update the table without creating a new one. And of course any difference in the formatting will break the link to the PT, whereas in a macro I can validate that the data is in the right format easier.

[Paul_Hossler]

1. The PT can use a dynamic named range as a data source

2. I'm not understanding about the "difference in formating will break the link". What kind of formating breaks the link?

Possibly a 'hybrd' approace? A macro to validate / format / etc. the data, and then have it refresh the PT?

Paul

[SilverSN95]

Paul, I think a "hybrid" approach as you mentioned might work well. I've found that I can refresh the PT with macro, say after the data WS is changed, and this makes building my formulas very easy.
Do you happen to know if the pivot table is dependent on the same column order, in other words will I need a macro to order them before every data refresh?
Thanks again.

[Paul_Hossler]

I think that there is some dependancy on the data content, but not order.

A little test of cutting and insert columns 'broke' the PT until I re-generated the dynamic data range using a macro that adds a new WB Name


=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))


What happens is that cutting the column changed the base cell (A1) to something else (C1). That caused one of the columns in the PT to be outside of the PT's data source

Redefining the Name again back to A1 made the PT able to be refreshed

You could easily combine the Name part with the Refresh part in a macro. You don't really need the Name, since you could just go to the PT Data Source directly with your rearranged data

Just try a little experimenting with a small amount of data and a simple PT.

Paul