Category error: Invalid: Input must fall within specified range

Ask Anything
1

On my transactions page, in Google Sheets, in the category column, most are marked red with the text

Invalid: Input must fall within specified range

What does this mean? How do I fix it?

Thank you!

2

Hi @abe.deshotel Welcome to the community.

Assuming you have created a budget with your own custom categories on the Categories Sheet, double-check the presentation of the Categories with the red flags on the Transaction Sheet against their match on the Categories Sheet. They must be exact, including the use of spaces. The flags indicate the contents of those cells match nothing on your Categories Sheet. Because the flags appear intermittently, that’s a sign your references between the Category column on the Transaction Sheet and the Categories Sheet are probably sound. The problem is with a few of the Categories on the Transactions Sheet. (Did you, by chance, recently manually edit a Category name or spelling in the Categories Sheet?)

While the Transactions and Categories sheets are linked, they are not dynamically related. So, while a change to a Category name on the Category sheet will appear automatically in the Category drop-down, past applications of that category to transactions are not updated; these must be done manually.

In a cell with a flag, click to find and reselect the edited version of the appropriate Category in the drop down, and the flag should disappear. Sort the Transactions Sheet by Categories to group them together to make editing easier. ( After grouping, change the first in a series to be corrected and drag downward to include all that are alike.)

Does this help?

3 Likes
3

Sometimes the validation (dropdown menu) also breaks if you insert rows in the wrong spot on the Categories sheet.

Double check your data validation rule for the Category column using these steps:

  1. Review the location of your Category column in the Transactions sheet and Categories sheet. What column letter does the Category column correspond to in both sheets? Usually in Transactions it’s column D and in Categories it’s column A. You’ll need to adjust the range references below to reflect the location of your Category column.
  2. In your Transactions sheet, select the Category column starting with row 2 and drag down the column to select the rest of the cells in the column. (Don’t select the top row “Category” header cell.)
  3. Open the Data Menu
  4. Choose Data Validation…
  5. Configure the cell range. If your Category column is in column D (most cases) “Transactions!D2:D” (without the quotes). Using D2:D, instead of D2:D355 (or some other row number) will ensure that all new rows added get the validation too.
  6. Set the criteria. This will always be “List from a range” and in most cases will be “Categories!A2:A” (without the quotes).
  7. Click Save
3 Likes
4

Thanks everyone,

It seems fixed now. I followed this and I suppose I had some things set wonky and didn’t realize it.

5

Heather
I have the same problem, except that it starts in the first entry for 2023.
What did Tiller do differently this year??
How to fix??
Will send screen shot to your email.
Rob