Just be clear, Excel 2007/2010 supports over one million rows per sheet. I'd hate to be the person doing a detailed transactional reconciliation with that many rows.
As you've discovered, you cannot build a filter that relies on RowNo() in any way, shape, or form.
In the past, solutions to this sort of challenge rely on filtering with some other key field, such as Customer names starting with "A", then "B", then "C", etc. or some other device, so as to develop smaller selections.
To facilitate the export, when you do get a set of filters that satisfy your requirement, try to duplicate the Table structure (assuming that you're trying to export from the Table) as a summary, then duplicate the initial summary, changing the associated filter as necessary. Then you can build a project export that exports all summaries as different sheets in a single destination file.
What field can you use in your data set to use as the basis for smaller sets?
I was afraid it wouldn't work any other way and had my heart so set on doing this that I didn't even think about sorting based on an alpha split on the consumer's name. As for exporting all of the filters into a single workbook, I'm already doing that.
Thanks so much for your help. You are so knowledgeable about Monarch that I know I can depend on what you suggest.
Have an awesome day.
Filtering won't work with Rowno as the Row Number is only calculated after the filter is applied. It will work fine using Recno - the Record Number is the row from the unfiltered table.
A calculated field WORKSHEET = 1+INT((Recno()-1)/65000) will give a value of 1 for the first 65,000 rows, 2 for the next and so on. You can then export filters for different values of WORKSHEET.
Hope this helps,