I was recently contact by a regular ExcelWithMonarch.com reader, Nigel Winton of England, who relayed how he used Monarch to create an interesting solution to a challenge given to him by a co-worker.

Here’s his story…

I have one person who is always requesting something new. You know the type: flexible goal posts with no fixed abode.

Recently he asked for a report which would include the required data, along with a summary of the data at the bottom of the report. He did not want the data and the summaries on separate pages, as would be the easy way with Monarch. He also wanted to be able to email the report, and asked that the report print on one page.

It would be possible to create this report with Excel if you have the patience to create many formulae using the SumIf function, but it would be time consuming and somewhat messy. Plus, as the data being summarised could change almost daily, the SumIf approach was difficult to keep up to date. Also, he did not want to see a line in the summary if there weren’t any non-zero values, so constant maintenance would be necessary.

So out comes the trusty old Monarch V9 tool now and I set to work.

I found that, providing I used the same data type, I could append summaries to the end of the exported table. The only proviso is that you must have the same title for the column you are appending to in your summary. That’s not a problem; use Monarch’s ability to change the column title in the summary from the default and it works.

You can set up calculated fields in the Table for things like the word Total that might not be anywhere in your data. Hide them in the table and use them in the Summary to make the presentation easier to read.

It is also useful for putting a total at the bottom of a table when exporting it. Just create a single line summary to total the spreadsheet and append this to the exported table. Make sure you have a character field available to the left of your numeric columns that you can use to put the word Total in, and there you have a table with totals.

If you have numeric fields that you are not totalling, you could do some calculations in a summary and append this under the total line. None of these exported fields will have formulae so they can keep the Monarch Table export look.

In the supplied example, you can see the way in which I have set up the various summary appends, then changed the column titles to make them fit onto the table. The colours are not compulsory, but they can be pleasing to the recipient of your work, who will think that you are being really nice by doing all of this extra formatting work for them.

You can append data from different reports and models providing you keep to the rule of same data type and field name. I have experimented with exporting with the Drill Down and Formula options set for the Monarch summaries, but will leave you to find your own way with those.

This set of files (please see the links below) is by no means up to the limit you can use, but will start you on a path that can bring a feeling of satisfaction that you have produced a meaningful and useful report.

You will need to save the files on your C: drive in a folder named ‘Monarch Demo’ for the Monarch project file to work properly.

Please contact me via Sandy or a Private Message on the Monarch forum if you require further details.

Shake It Up

Nigel’s solution is a terrific example of what you can do when you use Monarch creatively. While the desired layout of the report he described could be created with Excel or by other means, by using Monarch he can regularly publish his report much more quickly and easily.

How will you apply his techniques to your own work and excel with Monarch?

————————————————————————————–

Links (Right click and select Save Target As):

Append Demo Report

Append Demo Monarch Model

Append Demo Monarch Project

Append Demo Spreadsheet

Append Spreadsheet