Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The best way forward?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The best way forward?
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 On Sep 28, 9:55 pm, "Mickey" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The best way forward?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The best way forward?
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/ExcelTutori...9/Default.aspx These should be enough to get you started. -- Regards Roger Govier "Mickey" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The best way forward?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The best way forward?
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. -- Regards Roger Govier "Mickey" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date going forward | Excel Worksheet Functions | |||
HOW DO I GET A BALANCE TO C/FORWARD TO NEXT WORKSHEET | Excel Worksheet Functions | |||
hyphen to forward slashes? | Excel Discussion (Misc queries) | |||
Carrying a balance forward | Excel Worksheet Functions | |||
Page forward goes backwards | Excel Discussion (Misc queries) |