The best way forward?

  • Thread starter Thread starter Mickey
  • Start date Start date
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
 
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
 
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
 
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.
 
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
 
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.
 
Back
Top