Tough Project!

scouse

Board Regular
Joined
Jan 16, 2004
Messages
207
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!!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
Have a look at Pivot Tables. Should cover most of what you are looking for
 

scouse

Board Regular
Joined
Jan 16, 2004
Messages
207
Erm,
the fact that I have to publish these onto separate tables for managers would this prevent me using pivot tables?
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
No, use the pivot table to manage your raw data, then format as requd.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,897
Members
431,772
Latest member
dannyboi1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top