Tough Project!

[scouse]

I have been given a project which I have absolutely no idea how to go about it!

My company is nationwide and is organised on a district to regional basis. Every month there is a sql query thrown into excel which lists in Cols A-D



- business code AB123 (A = programme type, Bxx=Region, the final x=district)
- subhead code A01-A12 e.g. A01 is Salary
- monetary value
- Name of district

The end result should be a number of district tables (which will feed into regional tables, then national tables) with a list of programmes on the left hand side, along the top is listed the subheads, then there will be some calculated fields - these would contain the financial details of each district per programme per subhead.

Problem - all of it! Esp the fact that the managers want it future proofed so that any new districts will be pulled out as well (e.g. someone may set up AB1237 (7 being the district number). As far as i can make out the maximum possible disticts a region may have is 27 variations
AB122x, AB123x, AB124x

If anyone can even point me in a direction i will be happy!

e.g.Can you use vlookup to look for a range of values for each district?

apologies and thanks in advance!!

 

[SteveO59L]

Have a look at Pivot Tables. Should cover most of what you are looking for

 

[scouse]

Will do - actually something i havent looked at before....!

 

[scouse]

Erm,
the fact that I have to publish these onto separate tables for managers would this prevent me using pivot tables?

 

[SteveO59L]

No, use the pivot table to manage your raw data, then format as requd.

 

[BrianB]

Think in terms of a single table with additional columns as required to enable analysis. You can then use pivot tables/filters etc. The table should contain data for a whole year with a column for "Month". Might need to be in Access, but easy to do.

You want to set this up so you do not need to change the structure each month. A good way is to have a pivot table with a blank record with just the month in its column. This can then be used as a simple lookup table to transfer data to month columns in reports.

If you have to produce summary reports then make them all the same worksheet which can be copied. So a Region sheet might be the sum of the Area sheets that make it up. Include blank columns for all 12 months (can be hidden if necessary). The Year To Date total will be the sum of 12 columns. Use a formula like this to return zero for missing values, as well as coping with changes in number of rows :-
=IF(ISERROR(VLOOKUP(A1,PivotSheet!A:Z,2,FALSE)),0,VLOOKUP(A1,PivotSheet!A:Z,2,FALSE))

The top level report might be a workbook containing copies of all Regions report sheets with a sheet summarising those.

This is obviously all conditional on what you want to achieve, but I have used this basic method for many years and situations. It is also easy to "drill down" when someone at the top wants to know about any particular number or variance for the month.