Creating summary reports with charts & numbers from Excel data

Try using a pivot table with the country name in the page feild. I do this sort of thing quite often. I've created user friendly formated sheets with charts etc. linked to cells in the pivot table. In the cell next to the page field enter a formula to show the page feild selected e.g. = A1 then in the chart click in the middle so the chart is selected then enter a formula = the page feild cell entered on the Pivot sheet, the chart will link to that cell. So when you change the country the country name will show on the chart. For some reason it doesn't link directly to the page feild. In excel 2000 you can create pivot charts which might solve the problem. Everytime a new country is added extend the Pivot Table data range (Click in the middle of the Pivot table right mouse click click back and extend the range). I have an idea (not tried) that if you name the data range Database it will automaticaly extend the data range whenever something new is added. If you not sure about what feids to put in the column, row and data areas of the pivot table click on help as you step through the pivot wizard.

Thanks for this. I tried something like this already and although its great for creating summaries one page at a time it cannot generate X summaries on separate pages easily and simultaneously (lots of manual selecting to be done). I think I may have to create one master summary sheet which links charts and text to named ranges and then copy it manually X times for each country changing the page field item each time. Hmmm….

PS. Although Excel won't allow you to select the page field item directly you can do this either by (1) selecting the cell to the right and pressing the left arrow key manually on your keyboard or (2) selecting any cell and manually changing the cell reference in the formula bar.