# Tough Project!

#### scouse

##### Board Regular
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?

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Have a look at Pivot Tables. Should cover most of what you are looking for

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

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

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

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.

Replies
3
Views
310

1,218,920
Messages
6,145,225
Members
450,602
Latest member
AceSpace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back