I updated my transactions in my Tiller Money today after being idle for last 90 days, and noticed that it is not categorizing each transaction and the drop down list for categories is blank. In the Help section I found instructions that ssay to copy the transaction description and going to the Autocat page, paste the description copied andassign the category. However, all the categories and descriptions appear on the Autocat page correctly but have a small red triangle on each cell with a message that says: Invalid: input must fall within specified range.
I have not been able to find any other troubleshooting on this matter and find myself stuck.
1 Like
Sounds like something happened to your āCategoriesā sheet. The Category drop downs you get in your āTransactionsā sheet uses something called āData Validationā, which in this case, points to your āCategoriesā sheet and grabs the list it finds there. Do you see a āCategoriesā sheet, and if so, are all your categories listed in column A?
I think @jpfieber is onto the scent. It sounds like maybe your data validation reference has broken in the Transactions sheet. If you arenāt seeing your categories (from the Categories sheet) in the dropdown (in the Transactions sheet), youāll want to select all of the categories cells (cell 2 to the bottom) in the Transactions sheet and then go to Data / Data validation to link the data validation range back to your categories in the Categories sheet.
I was having a similar issue in that all of my categories were not showing up in the drop down choices in the transaction sheet or in autocat. I recently moved over to using a Foundation Sheet and created many new autocat instances within Autocat Beta. Is this, possibly, a bug within Autocat Beta? I did what @randy suggested and re-validated the (now longer) data range. Hope this helps!!
So it is working for you now, @Jeff_G?
Yes it is! It seems like the data validation reference in my sheet was also broken. I followed your suggestion for my fix -
" I think @jpfieber is onto the scent. It sounds like maybe your data validation reference has broken in the Transactions sheet. If you arenāt seeing your categories (from the Categories sheet) in the dropdown (in the Transactions sheet), youāll want to select all of the categories cells (cell 2 to the bottom) in the Transactions sheet and then go to Data / Data validation to link the data validation range back to your categories in the Categories sheet."
Thanks so much.
Glad to hear it, @Jeff_G.
Iām currently having the same issue as the OP. I am not seeing all of my categories in the dropdown menu on the Transactions sheet, but they do appear on the Categories sheet. For example, I have a category called āDues and subscriptionsā and I want to assign that to my Google storage charge. All previous instances of āDues and subscriptionsā are marked with a small red triangle with a message that says: āInvalid: input must fall within specified range.ā
I did follow the steps @randy mentioned - I selected all of the categories cells in the Transactions sheet and went to Data/Data validation. That might have been where I got tripped up because I wasnāt sure what I was supposed to do with the Data validation settings and simply clicked on āSaveā. That had no effect on the error message though; Iām still seeing the red triangles. Is there something I need to change on the Data validation tab to make this work? Thanks in advance!
Ok, I see what the issue is. Iām using the Foundation template, and apparently had copied some of the categories over from another Foundation template Iām using because on the new template, Iām not seeing the green arrow in the Type column of the Categories sheet which brings up the dropdown menu (Income, Expense, Transfer). I had copied āExpenseā etc. from the Categories sheet in the first Foundation template, but those entries donāt have the dropdown arrow and donāt show up in the dropdown list in the Transactions sheet of the newer template.
Anyone have an idea about how to fix this? Iām stuck at this point so all input is appreciated.
Select all the cells you want to fix in the Type column (eg. C2:C100)
Select āData Validationā from the āDataā menu
For āCriteriaā, choose āList of itemsā and in the box enter āIncome,Expense,Transferā
Click āSaveā
You should then see those items in a drop-down in each of the selected cells
Thanks @jpfieber. That was the missing info I needed for the data validation thing. I followed the steps and now I do see a green arrow in the upper right corner of all of my Type entries. However, those items are still not showing up in the dropdown menu on the Transactions sheet. I tried closing and reopening the template but no change. Any ideas?
The Transaction sheet āCategoryā column has itās own data validation, so youāll need to go through a similar process.
Select the cells you want to alter. In my case D2:D6366
Select āData Validationā from the Data menu
For Criteria, this time choose āList from a Rangeā and in the box enter =Categories!$A$2:$A$200
Click Save and you should see your categories show up in the dropdowns
1 Like
As always, everything @jpfieber said is spot on. The only thing Iād add is that, in Sheets, you can usually not specify the end row and your range will extend to the bottom of the sheet. So, rather than D2:D6366, just use D2:D. Wish Excel did this too.
As @jpfieber mentions, make sure the column letters match up to the right headers in your spreadsheet
I can follow up until hitting the data validation from the drop down menue. I keep seeing you guys referenceācriteriaā but I see no such promp when I hit the data validation - in fact nothing happens for me.
I donāt understand most of what you guys are talking about. Itās pretty frustrating not knowing how I was even able to mess something up. Only some of the categories in my transactions sheet and auto cat sheet have these red triangles that give that ā `Invalid:
Input must fall within specified rangeā.
I just tried to add a transaction and those categories are unavailable from the drop down, I need help.
I apear to have solved it. Not sure how it got like that though. The Categories reference was going from =Categories!$B$8 not =Categories!$B$2
1 Like
That sometimes happens if you add new rows above the first data row that anchors many queries in other sheets. Good sleuthing and apologies for the hassle. 