I am using Microsoft Excel. I have transactions from Money Feeds from mid July for some accounts thru current time for all accounts. As far as I can recall, I have been able to sort all rows in the Transaction tab with expected results up until today.
Today I have pasted in additional rows at the end that I am migrating from my Mint account, for January 1st through October 31. I got all the categories used in Mint defined in the Categories tab, and pasted the rows in, and it mostly seems okay, except:
-
When I go to Data > Sort, and try to sort all the data rows by date, it sorts the Money Feed provided rows that are at the top, but does not sort the manually added rows. I can select the manually added row range and sort them. As soon as I select any Money Feed provided rows along with any manual rows, the Sort button is disabled on the ribbon and the sort cannot be done.
-
I also notice that when I popup the filter on the Categories column it only shows the categories I have used on the rows added by Money Feeds, not for the rows I added manually; somehow the manual rows I added are not being considered for filtering.
Is there something I was supposed to do in order to “bless” the manually added rows, so they would be treated similarly to the Money Feed provided rows for almost all purposes?
I don’t understand why the sort would be restricted this way. Is it a performance optimization for the addin, to segregate the Money Feed rows at the top so when the first manual row is encountered, the addin does not have to look any further for rows with a Transaction Id? I need a more correct mental model of what is going on in Tiller and Excel to help me understand this. I don’t remember any of the documentation saying that all manual rows need to be added at the end after all the Money Feed rows with transaction Ids. It is okay with me if they need to be segregated for performance, but I need reassurance that that is the reason, and it is not just a bizarre bug, or something else I don’t understand.
Please help me understand what is going on and why it is this way.
Thanks, Robert
The only reason I can think of for this happening is a blank row before the manually entered transactions. Make sure you have no blank rows in your transactions. You might not notice one if you have filters enabled, so turn off filters first to be sure you’re seeing everything.
Hi jbfieber, I think you are right that I inserted or pasted the manual transactions after a blank row, or otherwise failed to insert them into the defined Excel data table. I’ve backed up and inserted the manual rows into the middle of the defined table, and that works; I’ll post a solution to my problem. Thanks, Robert
1 Like
I have solved this problem. The root cause was that I did not realize that Excel “data tables” involve more than just formatting. The Tiller Foundation Template has done a Data > Format As Table command to define a rectangular table on the Transactions sheet that contains the rows that have been added by the Money Feeds filling the data from the enabled accounts.
Once the formatted table is defined, it affects paste, insert, delete, sort, and other operations. This operations don’t have well defined meaning if they contain both table data and non-table non-empty cell data. Sometimes the operation will give errors when both in-table and out-of-table data are selected; sometimes the operation will only operate on the part in the table. For example, it appears that sort will only operate in the table when there is only one table defined on the sheet.
The table can have a last column, and in my experience it always has a final row.
When I added my manual rows, I added them after the existing rows, and there may have even been an empty row between the table and my new manual rows; as jbfieber suggests above, thanks to him for his suggestion. But my new manual rows were not part of the table, and once I understood the Excel table feature better it was obvious - the table rows alternate having no fill color and having a light green fill color, and my manually added rows at the end did not have this alterating fill pattern table formatting.
The solution to the problem that I used was to insert the manual rows before the 2nd row of Money Feeds rows in the table, and this caused the table to be expanded to include the new rows. Then I can sort the rows in the sheet (aka in the table) in any way desired.
Regards, and thanks again to jbfieber, Robert
1 Like