3 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    External Look Up Missing Lines

    beckyz _

      I have an excel spreadsheet that was compiled by running monthly text files in Monarch and appending the export to the spreadsheet each month (so far Jan-July).  Recently, I manually added some lines to the spreadsheet (not by exporting from Monarch) and saved the spreadsheet.  When I use this spreadsheet as an external lookup for another model, the manual lines are not being picked up on the look up.  Any idea why this would happen?

        • External Look Up Missing Lines
          Data Kruncher

          Hello and welcome!

           

          I would speculate that the model doing the lookup on the sheet that you added lines to manually is likely using a named range defined in the spreadsheet to find the source data.

           

          Monarch normally resets the area for the range name when it exports. When you added data manually, if you added your data at the bottom of the list, you likely added the data outside of the range defined by the name.

           

          Open the file to which you added data manually, go to the Insert menu, select Name... Define

           

          Now select the name already defined (there's likely only one) and revise the range shown at the bottom of the window (Refers to) to include the rows you've added.

           

          Sorry I don't have the time to run a full test just now.

           

          Please let us know if this is the nature of (and solution to?) your problem.

           

          Kruncher

          • External Look Up Missing Lines
            beckyz _

            Thanks!  It worked perfectly after redefining the range on the spreadsheet.  Learned something new.

            • External Look Up Missing Lines
              Grant Perkins

              Becky,

               

              Just to augment Kruncher's advice, here is what the Monarch Help has to say on the subject of lookup link from xls files ...

               

              "Note: When joining data from an XLS file, the list of tables may include what appear to be duplicate table names, one with a dollar sign suffix. The table name without the dollar sign represents a named range in the XLS file. The name with the dollar sign represents the worksheet name. Typically, both names will point to the same data, but in rare cases, the range name may point to a different data set. When in doubt, select the name with the dollar sign suffix. "

               

              Basically it is good to have both options available so long as one is aware of the different benefits they bring to the party!

               

              HTH.

               

              Grant