The best way forward?

M

Mickey

Hi,
Please could someone advise me on the best way forward to do this?

I have 25 'areas' and some 20 'categories'. I need to display these so that
each area can look at it's own performance or look at the categories across
each area. I was wondering if there is a recommended way to say create two
worksheets, one for areas and one for categories, then as an example allow a
user to select an area from a list and have the display then show the
required data.

For example, on the categories sheet, the initial display would be the sum
of all catrgories across all areas, then a user could select an individual
catrgory and see how each area has performed.

Would the best way forward be vlookups. index/match or database functions?.
Any advise would be greatly appreciated. Also any good links to study the
suggested methods?.

Thanks,
Mickey
 
P

Pete_UK

Can you have 25 Areas going down the sheet and 20 Categories going
across, thus creating a table? You could use a pivot table for this or
a SUMPRODUCT formula to get the sum in each Area/Category
intersection.

I'm not really sure what you are after, but hope this helps.

Pete
 
M

Mickey

Hi Pete,
Many thanks for the response. Sorry for not explaining it clearer. What I
was looking for was the best way to have the 25 areas in a column and then
further columns of -

Area || 'This Year || Last Year || Change || % Change.
---------------------------------------------------------------
North
South
West
East

Then I wanted to enable the user to choose one of the 15 categories from a
list so that the table of 25 areas then displayed the performance results
for the selected category. This way I could then avoid having a seperate
worksheet for eac h area and/or category being measured.

Best Wishes,
Mickey
 
R

Roger Govier

Hi Mickey

As Pete has already suggested, Pivot Tables are probably the best way to go.

For help on Pivot Tables take a look at the following sites

Mike Alexander has some great video tutorials which will show you what they
are about
http://www.datapigtechnologies.com/ExcelMain.htm

Debra Dalgleish has lots of help in dealing with all of the issues within
Pivot Tables
http://www.contextures.com/tiptech.html
scroll down to the section on Pivot Tables

Ed Fereiro has a great tutorial at
http://www.edferrero.com/ExcelTutorials/PivotTableTutorial2003/tabid/89/Default.aspx

These should be enough to get you started.
 
M

Mickey

Hi,
Thanks Roger, however Pivot Tables are not what I'm after, they are great
for me and my reports but for the end user. I was thinking that the best
choice was probably linked to index/match or vlookup using a combo box
selector system, just not sure which is the best way forward. Many thanks
though for your suggestion.

Mickey
 
R

Roger Govier

Hi Mickey

I'm not sure that I see why PT's are fine for you, but not for the end
user!!!
With Categories set as a Page Field, then they would have a simple drop down
to select which category they wanted to view, in the format as you had set
out.
 
Top