Thanks for the feedback, @jemmoa7. Install was seamless on Excel too?
The only “issue” if you can call it one, was on Excel, the Category dropdown on the far right of the sheet was not set up. I set it up and all was great.
Jim
The description not working. it shows #Calc on the description and total
The category drop down is not set up in the excel version. I am also getting the #CALC! error in both the period total as well as description and total.
I fixed it by just copying the data validation values over from the tracker sheet. I’m curious as to why it is not automatically moving over when the sheet is moved. The same issue occurs in the new Account Filter sheet as well.
I reproduced the Category-dropdown issue. The disappearing Category dropdown on the copy operation seems to be an Excel bug. You can see that the dropdown is functional in the downloaded master. Then, the data validation, which contains a simple formula referencing the Categories sheet, disappears when the worksheet is copied into the new workbook.
Strangely, references to other sheets are preserved through the copy operation when they are in-cell formulas. It seems that only the sheet-references in the data validation are lost.
I don’t quite understand why the dropdown is disappearing but I found a workaround. I changed the range reference from a separate worksheet (i.e. the Categories sheet) to some hidden cells inside the Category Tracker worksheet (that are fed by in-cell formulas to the Categories sheet that seem more robust to the copy operation).
I have updated the master to version 1.61 (link above 
) with the following changes:
- Category dropdown references range within its own sheet
- Added
IFERROR()wrappers to Description lookups in case of category-not-found error - Removed “Utilities” prepopulated category from category dropdown
Thanks for flagging this, everyone.
Randy
P.S. The #CALC! error may stem from the fact that the dropdown in the master has “Utilities” pre-selected but that is not a category in your sheets. Do the formulas work when you select something valid? I changed the master to default to empty so it should no longer prepopulate with a category that fails.
I am new to Tiller and working on populating my file with information from Money in Excel. I did copy the Category Tracker tab into my workbook and ended up with a number of blank rows with zero’s at the top of the category list columns A:C starting at row 13. Took me awhile but figured out the driver was empty rows in the Tiller Categories tab below my active data. Passing onto others who may encounter the same issue. I really like this add-on which provides another source of evaluation. Thank you.
I’m glad you got to the bottom of this and thanks for flagging the issue in this forum, @dmelideo. I just added a filter for empty categories and bumped the version to 1.62 (link available in the first post) for future users. Feel free to update your copy, @dmelideo, or let it be as your workbook is working now.
Thank you Randy,. That was fast and unexpected. I could filter the Transaction tab for empty/blank category cells. As I’m moving Excel for Money data over, some previous categories will not match my transition into Tiller. I was just hoping there was an easy way to see if and categories did not match exactly match my Tiller lexicon.
I also previously used sub categories and have implemented the following work around of using the following syntax for a category:
- Category as GroupName: Category or Utility: Elec
- Group as GroupName or Utility
This allows me t sort by Group and keep the subs together. Also caused a bit of transition complication.
I love your Tracker add in, very useful.
Again I appreciate your help and will check out your update once my dataset is cleaned up.
Sounds like you are making good headway.
If I were trying to find categories in my Transactions sheet that weren’t live in my Categories sheet, I would add a column to the Transactions sheets and create a formula like =ISNA(MATCH(C2,Categories[Category],0)) and then filter on rows giving a TRUE value.
Your workaround on subcategories is interesting. On our Sheets solution, many users create a Tags column for this purpose. We have some templates that are helpful for filtering data on tags in Sheets. That isn’t yet built out in Excel.
Thank you Randy and Team for your help and suggestions. I will try the formula suggested and take a look at your Sheets solution. As I’m really getting started, I am not familiar with all of the tools or resources.
I did encounter another interesting quirk with AutoCat (at least for myself), sorting the list impacts the accuracy of Category updates.
Example: I purchase groceries and gas at a local grocery chain. The transactions come in with a store ID along with “Gas” if a purchase at the fuel pumps. Examples below are reflected as Category & Description from my AutoCat tab separated by a comma for communication clarity:
- Groceries, Weis
- Auto Fuel, WEIS MARKETS 188 GAS
- Auto Fuel, WEIS MARKETS 182 GAS
- Groceries, Weis Markets 188
- Groceries, Weis Markets 182
- Auto Fuel, Gas
All I’m trying to do is identify a transaction category as Gas or Groceries
The lowest entry in my AutoCat table supersedes all other entries. I have ended up keeping the “Auto Fuel, Gas” at bottom of my table so it correctly identifies the Fuel transactions. If sorted with the entry at top of my Category List, then all Weis transactions end up with Groceries as the category.
Thank you very much for your quick feedback. I have found your Category Tracker very useful to easily identify costs by category so U can develop an actual spend for budgeting purposes.
AutoCat processes rules from top to bottom. It will apply the overrides of the first rule where all the transaction meets all of the non-blank filter criteria. So a broad rule (e.g. Amount > 100) at the start of your list could preempt all subsequent matching rules.
@randy That depends (at least in Excel). If you select option to only fill Uncategorized Transactions then yes, the first rule that matches is filled and any later matches are ignored. However if you select to fill ALL Transactions, then the last rule that matches overrides all previous rules… so it is essentially the reverse.
Edit: I checked again and I see inconsistent behavior between Manual runs and Run on Fill. So it looks like there is a bug here because it is not consistent behavior.
This is great information, As i’m migrating information, I have been using the AutoCat to assist in consistency. Once I feel the dataset is cleaned up, I do intend to only auto update new transactions,
Thanks for flagging this, @Alex.Mtz. We’ve got an engineer looking into this this morning. He seems to be able to replicate the behavior.
Hi Randy, I did try your formula with conditional formatting to color the cell red and it worked great. I did run into issues when importing new transactions and running AutoCat this morning. After trying a few things I removed the column with math from my Transactions tab. Autocat then populated the empty cells. I went back and read the AutoCat document and realized that it will not run if there are any math functions under the title row. Did I misinterpret how you applied the formula mentioned above or is there another way to execute getting True/False values which I can use to flag a missing Category? I am using Excel and need to remember that as many of the Tiller documents are focused on Google Sheets at this time. Thank you again.
I don’t have timeline but we are working on the formula vs. AutoCat issue.
As for the formula, I was imagining that as a one-off to identify migration issues then something that would be deleted.
1 Like
Thats exactly what I did and it was very helpful - Thank you
I am attempting to install this worksheet, but O365 Excel does not show “Move or Copy” as an option when I click on the worksheet tab. Do I have to do this all locally (i.e., download the Category Tracker worksheet locally and open my Tiller O365 Template ‘in Desktop App’) to install this new worksheet?
Yeah, installing templates seems to currently need to happen in the desktop app. Once copied to your template, most should work fine in the web app.