I only have 89 Categories, however, only 77 Categories show up or are available on my Transactions Sheet Category column. Is there something I am missing that is limiting the view of all of my Categories? Is there an easy fix?
Hi @begatt:
Might be…try this…
On your Transactions Tab, click into the cell in the Category column of your first row under the header row. In the Data menu, select, “Data Validation.” In the dialogue that appears, under Criteria, there should be two boxes. In the box on the right, do you see a formula that looks something like this?
=Categories!$A$2:$A$203
This is the formula that creates your Category list for each cell in this column. I imagine that, instead of 203, the last number in your formula may be 78? If it is, just change the 78 to something like 200 to include your last category and beyond, and click Save.
Also, check to be sure that this data validation rule is applying to your entire column. At the top of the same dialogue, in the box for the Cell Range, there should be a formula that looks like this:
Transactions!F2:F (F is the column for Category in my sheet. Yours may be different.)
Let me know if this helps.
Best.
2 Likes
Yes, you are correct (thank you)…it is/was set to only 78. How then do I apply this to all of the cells in the Category Column. I can’t just copy and paste as it will overwrite all of my selected Categories. Is there a single. Even Copy > Paste Special doesn’t have the option to copy “Validation”…?
Figured it out…I had to select all off the cells in the column and then change the validation with all of the cells selected.
Good work, @begatt !
To be sure you are applying the rule uniformly, you’ll need to confirm that it is being applied to the entire column, including new rows that are added on updates. This means, after you have selected a cell in the Category column, return to the Data menu, Data Validation. Check the box for the Cell Range, there should be a formula that looks like this:
Transactions!F2:F
The formula should reflect the column letter in your sheet that is the Category column. (F is the column for Category in my sheet.) If this formula is present and referring to the Category column, you won’t need to copy and paste the other, since it is added automatically to every new row. (Recall that on updates, new rows of data are added at the end of the sheet and then resorted automatically to the top, most recent date first.)
1 Like
Yes, it says Transactions!E2 in cell E2 and Transactions!E3 in cell E3, etc.
1 Like
Awesome. Great work, @begatt