MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Creating summary reports with charts & numbers from Excel data

Posted by Jaspal Jandu on August 04, 2001 11:03 AM

I have a country database in Excel which lists many economic (e.g. GDP, population etc.) variables per country (212 in total). The database is updated every quarter.

I would like to produce one page summaries for each country in a printer friendly format (i.e. a one page overview of Turkey, for example, would include: country name, population, a few charts showing GDP over the last five years etc). All the data required for each summary is in the database.

I tried to link the Excel data to a Word mailmerge document, and whilst this produced the 212 reports very quickly for fields containing names, it could not create new individual charts for each country (even using ODBC links).

Can I solve this problem solely in Excel??

Posted by Malc on August 04, 2001 12:56 PM

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.

Posted by Jaspal Jandu on August 04, 2001 2:36 PM

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.