Creating data tables from a database without using pivot tables

Hi Team,

I will try and explain this as best as I can. Essentially I have a table of data (database) (in excel) that I would normally use to produce pivot tables to display particular breakdowns of the data - as we all would. However, my problem is that a specific person who receives these tables cannot deal with them and does not wish to receive them, however they want the same information. So I need to recreate tables that summarise the data manually.

An example is that I have countries as one variable and budget as another variable. Some additional variables are month and brand (there are others but not relevant right now). So what I want to do is create a table and then in the first data cell look for all rows in the database that are from country=Australia and brand=BrandX and sum the budget for all of these cells. Essentially I want to include a formula in each individual cell to recreate what a pivot table does in a jiffy.

My real question is how to start. My strategy if you will. Should I be looking at using lookups and ifs and a combination of all these things, or should I be learning to do this in VBA. Basically I'm looking for the path of least resistence while producing something that works even when new data is added to the database. The table is named CPtable so I can reference it directly.

Any strategy ideas or help would be greatly appreciated! I realise this is a question with a large answer!

Thanks

Gareth

Hi Gareth

I have a similar problem with my principles - I use filters and subtotals to get the relevant data.

You could use drop down lists for the elements that they want and then run a sumproduct from the drop downs to deliver say budget.

It depend how much data that you have, it could be unwieldy

edvwvw

I recommend SUMIF or SUMPRODUCT. They are good alterrnatives