8 Replies Latest reply: May 15, 2014 10:00 AM by Data Kruncher RSS

    Summary Export to Excel/Nonblank Cells Notice

    mcandrje _

      Whenever I export a summary to Excel now, I've started to receive a MS Excel note that I can't format the worksheet 'because Excel cannot shift nonblank cells off the worksheet'.  I don't know what's causing this, but I imagine it must have something to do with the way I'm formatting the summary in Monarch?  I didn't have this problem with Monarch V7, but now that I have V9, I notice it happening more frequently.

       

      Does anyone have an idea as to what could be causing this and how to correct it?

       

      Thanks for any help!

       

      janet

        • Summary Export to Excel/Nonblank Cells Notice
          Data Kruncher

          Hi Janet, and welcome aboard.

           

          I've seen that particular breed of nasty error in Excel before, but never in using a file created by Monarch.

           

          Here's a couple of ideas to get started. First, I would see if you get the error again if delete the existing file first, before exporting from Monarch (instead of overwriting it).

           

          Second, unless you [I]really /I[/B]need the formulas included in your exported summary, I'd set the export format (Options, Export and Clipboard...) to Excel 97+ (Monarch V8), and see how you make out with that.

           

          HTH,

          Kruncher

            • Summary Export to Excel/Nonblank Cells Notice
              mcandrje _

              Thanks for your help with this.  It seemed to solve the problem - at least on a temporary basis.

               

              I do have one other question that I'm being asked by my management.  I have a summary on a model where I've summarized the revenue totals for each of our salespeople, but they now want the salespeople listed on that same summary in groups under their respective managers.  Is it even possible to group in a summary after you've already summarized the revenue totals for each salesperson?  I have Monarch V9.  Thanks so much!

               

              janet

                • Summary Export to Excel/Nonblank Cells Notice
                  Data Kruncher

                  Yes, just add the manager's name as a key field above the salespersons name. Now double click the manager field in the key list, go to the display tab and check the insert subtotal line box.

                   

                  OK the whole thing and it should be good. Don't forget that you can use the Drill Up and Drill Down buttons in the toolbar when you have multiple key fields.

                  • Summary Export to Excel/Nonblank Cells Notice
                    Grant Perkins

                    Janet,

                     

                    Your question makes me wonder if I have missed something here so here's my suggestion. So long as Monarch is working with the sales values at the most detailed level you need  (say order value or order line value perhaps) you can do some amazing things very quickly with summaries.

                     

                    Assuming you have a 'code' of some sort in the Table to identify the manager for each salesperson (or an area that has a manager or whatever ...)

                     

                    Add another summary as a copy of your existing summary.

                     

                    Edit one of the summaries and insert the key field for the manager above the salesperson in the list of key fields. Now establish a subtotal break on change of code for the manager. that will give you the values by salesperson and the subtotals by manager.

                     

                    I'll leave it to Data Kruncher to provide guidance on the use of color to make the result really zing and leave your management in awe of your work! 

                     

                    But be warned, they may start to expect the (nearly) impossible!

                     

                    HTH.

                     

                     

                    Grant

                     

                    Edit: Dang. I new I shouldn't have gone back to insert that last sentence edit ... 

                      • Summary Export to Excel/Nonblank Cells Notice
                        mcandrje _

                        Grant and  Data Kruncher,

                         

                        Thanks for your reply and suggestions.  Unfortunately, there are no codes for the managers in the report.  I know which salespersons are assigned to which managers, but our system doesn't report on that information with a code or even a name of the manager.  That's why I felt it might be impossible to group salespersons under managers that the report doesn't even know exist.  Unless there's a way to create filters from the table records to assign the salespesons to certain managers, or create summary codes that don't exist on the report, then I don't know how I can make this work.

                         

                        I appreciate your help on this, but I feel I may be chasing a rabbit down an empty hole.

                         

                        janet

                          • Summary Export to Excel/Nonblank Cells Notice
                            Data Kruncher

                            Hi Janet,

                             

                            Are you using v9 Pro, or the Standard edition of Monarch.

                             

                            I've been using the Pro version for so long now, I'm sorry but I don't know[/B] if you can create a Lookup calculated field with the Standard edition. I [I]think /I[/B]that you can, so I'll carry on under that (perhaps incorrect) assumption.

                             

                            The solution is nicer and much more manageable for you, long term, if you have the Pro version, but as long as you can create a Lookup calculated field, you can do what you need to do: add new information into your report.

                             

                            In the Table window, create a new calculated field. Hopefully you have the Lookup option. Select it.

                             

                            Name the field Manager, and make it a Character field wide enough for your names. On the Lookup tab, make the Input field the Salesperson field. Click the "Get Input Field Values" button. Now type a Manager name for every line in the grid. If there are more Salesperson names that you know should be listed, but weren't in the particular report run that you're using, click the Add button and add both the Salesperson name as it will appear in the reports, and their manager. Click OK to save your edits.

                             

                            You can now use the Manager in the summary.

                             

                            Can you do this?

                             

                            Kruncher

                              • Summary Export to Excel/Nonblank Cells Notice
                                mcandrje _

                                Hi Data Kruncher,

                                 

                                Are you some kind of genius or what?  WOW - it worked, and it's totally made my day.

                                 

                                Thanks again for all your super, super help!  This is going to make some managers very happy.  (And what a great thing to learn, too)!

                                 

                                janet

                                  • Summary Export to Excel/Nonblank Cells Notice
                                    Data Kruncher

                                    You're very welcome Janet. I'm glad that this has worked out for you.

                                     

                                    But I must warn you. Now that you know how to easily add new, meaningful content to your existing reports, there's no holding you back.

                                     

                                    Everyone in your company will know that you can create stuff that your main computer system doesn't provide and thus you, /Bwith Monarch,can provide even better decision support than the "stock" reports generated by the system.

                                     

                                    What other useful information can you integrate into your existing data, that doesn't exist anywhere in your main system?

                                     

                                    Have fun playing, and learning, as you excel with Monarch.

                                     

                                    Kruncher