The slow thing seems to have resolved so I am taking that as a non-issue until proven otherwise.
This is my Quicken replacement since Quicken could not do what I wanted it to do.
The document is currently 48 sheets. All have columns A-AT (46 and will grow). Rows are from 70 to 17000 (only one is that long for a long-held since-cancelled credit card). There is only one Table per Sheet.
The sheets that do most of the calculations are the "accounts" sheets. There will be checking account, savings account, credit card, home loan, etc. Some of the headers are Date, Description, Transaction Total, Running Balance, etc. These do fairly simple calculations. Compute the Running Balance, compute sales tax based on a 4 column + 2 row HLOOKUP, add and subtract.
{=INDEX($D:$D,MATCH(TRUE,ISBLANK($A:$A),0))}
As part of each account sheet, I have a reconciliation area that looks just like an account statement. It begins with Previous (or Beginning) Balance, subtracts Payments & Credits, adds Purchases, calculates Ending/New Balance. The next line computes the total of the Purchases Not on Statement and adds, computes the total of Payments Not on Statement and subtracts to find Ending Balance. The array formula looks at column A (Transaction Date), finds the first Blank and returns the number in column D (Running Balance). If this number and the Ending Balance number are the same, you are Reconciled.
{=INDEX('SheetName'!$C:$C,MATCH(1,(A97='SheetName'!$A:$A)*(B97='SheetName'!$B:$B)*($R$100='SheetName'!$J:$J),0))}
This is used in a home loan sheet. In SheetName (the checking account that pays the mortgage), the loan payment is made in two rows: one is the Principal row and one is the Interest row. When I get the mortgage statement, I input the actual Principal and Interest numbers in the checking account. The array formula gets the amount from column C of the checking account. The matches are Date (A:A), Description (B:B) and the word Principal in the Memo column (J:J).
I don't understand the hesitancy to use INDIRECT (except as you explained it, of course). This is why I use it and I think you touched on it when you said you might use it. I have lots of accounts. If I add a new account, it is a pain to incorporate the account names/sheet names into the existing formulas. If I use INDIRECT, it is "automatic". I have a list of sheet names and can perform the same calculations on all of the sheets using INDIRECT. If I add a sheet, I add it at the bottom and sort (because inserting and deleting lines breaks all kinds of formulas). It is a beautiful thing!
The summing across a range of sheets may tip me over the edge. It creates too many problems with adding sheets and redoing formulas to accommodate those sheets. And, having to add rows and columns to every sheet (even those for sheets that may be closed accounts) is tedious and does not allow for easy growth.
I might have figured out the dates. As you said, in Excel the dates are stored as numbers. In my case, they are formatted as a date (m/d/yy). When I look in Numbers, The dates in AA and AB are formatted as Date & Time (with no Time). In A, the dates are formatted as Custom Format. There is a difference in the "Actual" also. See screenshots.
