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:
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.
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.
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]
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.