4 Replies Latest reply: May 15, 2014 10:03 AM by Data Kruncher RSS

    filtering an external lookup

    JoeMcG _

      The spreadsheet I'd like to reference in an external lookup has a few rows that are either blank or not part of the table I wish to reference. Monarch is unable to identify where the true column headings begin and so it assigns field numbers instead. If I could just skip to row 4[/U] the lookup would procede smoothly.

       

      Failing that, I know that I can source filter out the nulls in field1 and work it out. This seems so basic, but I'm unable to grasp the syntax.

      I've tried

      F1<>" "   but get operand type error

      F1.not. Null    gives delimiter error.

       

      Am I getting warm?

        • filtering an external lookup
          Data Kruncher

          Hi Joe,

           

          Yes, you're getting warm.

           

          There are a couple of ways to tackle this. The easiest way is to use Excel's named ranges to your advantage. Monarch can detect named ranges within a sheet and use only that part of the sheet for the external lookup data.

           

          First, open your spreadsheet, create a range name for your table, and then save and close the file. Now select the named range instead of the entire worksheet when defining the external lookup.

           

          Alternatively, you can can exclude those null rows with an expression similar to:

          .Not. IsNull(F1)[/CODE]

           

          As much as I'd like to say "go with the named range approach", I'm a bit hesitant because you need to be careful[/I] that the range name reflects the proper range every time your data changes as you have more or less rows in the table. So having Monarch exclude nulls automatically while using the entire sheet may be a better approach for you.

           

          If however this is more of a one-off, use the named range. Easy and quick.

           

          It's a case of six of one and half a dozen of the other.

           

          I told you that you were close.

           

          HTH,

          Kruncher

            • filtering an external lookup
              JoeMcG _

              I expected an " Aha!" moment  where I realize that I've succeeded in filtering Nulls at some point in the past. Your solution proves that I've been close, but never hit the target. I just imagined that I had solved it.

               

              I'm leaning towards the named range solution. I do have some control over the sheet. The range name would survive adding and deleting rows as long as no one deletes the first or last.

               

              Either way, I leave with two new ideas for the price of one. I appreciate your assistance.

                • filtering an external lookup
                  Scott Eshleman

                  I think that I read somewhere that Monarch recognizes BOLD font[/B] as column headings even when there are (filled or empty) rows above them. 

                   

                  It seems likely as Excel Best Practices(?) recommends creating your totals at the top of the worksheet above the data rather than below. 

                   

                  Kruncher, I would have bet almost anything that I read it on your site http://********************/[/URL]

                    • filtering an external lookup
                      Data Kruncher

                      Thanks for the update Joe. Glad that I could help.

                       

                      Scott, AFAIK, Monarch doesn't detect data based on font attributes, be it in templates or when scanning external lookup sources.

                       

                      Many people recommend creating totals above the data, and in certain cases, I'll agree: it can a good solution. But I'd argue that properly implementing the use of [URL="http://********************/xlreport/excels-subtotal-function"]subtotal functions[/URL] instead of sum functions can provide a better, more logical appearance; especially when combined with the outlining/grouping tools, as Monarch does when exporting summaries.

                       

                      It really depends on the purpose of the sheet. We've all been conditioned to look for totals at the bottom, so many people get confused when they're not there. Totals at the top are convenient to construct. For fast and easy, put them at the top. If appearance matters, don't.

                       

                      Generally they benefit the builder, not the reader.