You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Auto-fill Category name when entering a transaction?

I'm using Numbers budget template, successfully set up Transactions categories to match Budget tab.


Is there a way to get the Categories column field to autofill, the same way Description column ahead of it does? Or must a user use only the drop-down menu to navigate to, then select, the category for a new transaction entry?


If autofill is possible, how may I inform myself how to activate it or set it up?


Thank you!


iMac 21.5″, macOS 10.13

Posted on Jan 5, 2021 11:31 AM

Reply
Question marked as Top-ranking reply

Posted on Jan 5, 2021 6:17 PM

"Is there a way to get the Categories column field to autofill, the same way Description column ahead of it does?"


What way is that? My guess is you are talking about Numbers 'auto complete feature, which provides suggestion of what you could be entering in the cell, based on what you have in other cells in that column.


If that's the case, all you need to do is delete the content (and Pop-up- Menu format) from the cells in the Categories column, and treat that column in the same manner as you do Descriptions.


The Category labels are on a pop-up menu to ensure that each transaction is assigned to a named category, that the category name is not susceptible to typos, and that the category matches one of the categories listed on the Summary by Category table elsewhere in the document.


You can easily edit the pop-up menu cells to fit your categories list. The easiest way is to go to the Summary table and edit the Categories list in column A of that table to match the categories you want to use. When the list is complete, select all of the cells containing the list, click the Format brush, and set the cells' data format to Pop-up list, Then change the Start with menu item below the list to Start with Blank. he exit th Inspector.


Each of the selected cells is now a popup menu cell containing the same list of choices, each set to a different choice.


Click on the first cell and use the control that appears to its right to set it to 'none', then Copy this cell. (and reset it to the original value.


On your main table, choose every cell in the Category column, then Paste. Your menu is now in every row of Category



AUTO SELECTION


A formula based solution could be used to automatically select the correct category and insert it in the Category cell on each row.

The key to this solution would be a complete list of all possible outgo receivers and income sources and the category assigned to each.

Receivers to whom payments may be in several categories would require 'tags' attached to their names to express which category applies to each expenditure.


Once that is available as a two column list on a separate table, a VLOOKUP or MATCH/INDEX formula can be used to look up the receiver or source and return the proper category to the category cell containing that copy of the formula with no user action required in the Category column.


Regards,

Barry


Similar questions

3 replies
Question marked as Top-ranking reply

Jan 5, 2021 6:17 PM in response to mjanes88

"Is there a way to get the Categories column field to autofill, the same way Description column ahead of it does?"


What way is that? My guess is you are talking about Numbers 'auto complete feature, which provides suggestion of what you could be entering in the cell, based on what you have in other cells in that column.


If that's the case, all you need to do is delete the content (and Pop-up- Menu format) from the cells in the Categories column, and treat that column in the same manner as you do Descriptions.


The Category labels are on a pop-up menu to ensure that each transaction is assigned to a named category, that the category name is not susceptible to typos, and that the category matches one of the categories listed on the Summary by Category table elsewhere in the document.


You can easily edit the pop-up menu cells to fit your categories list. The easiest way is to go to the Summary table and edit the Categories list in column A of that table to match the categories you want to use. When the list is complete, select all of the cells containing the list, click the Format brush, and set the cells' data format to Pop-up list, Then change the Start with menu item below the list to Start with Blank. he exit th Inspector.


Each of the selected cells is now a popup menu cell containing the same list of choices, each set to a different choice.


Click on the first cell and use the control that appears to its right to set it to 'none', then Copy this cell. (and reset it to the original value.


On your main table, choose every cell in the Category column, then Paste. Your menu is now in every row of Category



AUTO SELECTION


A formula based solution could be used to automatically select the correct category and insert it in the Category cell on each row.

The key to this solution would be a complete list of all possible outgo receivers and income sources and the category assigned to each.

Receivers to whom payments may be in several categories would require 'tags' attached to their names to express which category applies to each expenditure.


Once that is available as a two column list on a separate table, a VLOOKUP or MATCH/INDEX formula can be used to look up the receiver or source and return the proper category to the category cell containing that copy of the formula with no user action required in the Category column.


Regards,

Barry


Jan 6, 2021 11:08 PM in response to mjanes88

Hi Marc,


Your thoughts are correct. You cannot enter text in a pop-up menu cell without deleting the current content (the pop-up menu) first.


If you are finding it more efficient to type the entry and click when the auto complete list has been narrowed to one, or a few possible terms, then delete the pop-up menus and enter the categories directly.

Do remember that typos will be included in the suggestion list until they are removed from the column, so when you notice a misspelled or incomplete category name being suggested, make a not to track it down and correct the entry that's causing it.


"Using" a set of pop-up menus on the Summary table would only be useful on two occasions:

  1. Converting the list in the category cells to a set of Pop-up menu cells provides you with a cell that you can copy, then paste into all Category cells in the Transactions table, should you choose to use pop-ups there.
  2. Should you wish to change the order of items on the pie chart created from the Summary table, it would be easier to reset each of the pop-ups to the categories in the order you want them to appear than to delete and retype each category in the correct cell.


Regards,

Barry


Jan 6, 2021 8:57 AM in response to Barry

Thank you, Barry. I tried to adhere to your directions and successfully deleted the control + pop-up content in my Transactions tab Category column. I have a dozen entries already for this new calendar year, so I returned to the top of the Category column and individually entered category names carefully confirmed to match the spelling used in the Summary sheet. As expected, with thanks to you, each time I began an item which repeated a prior category name, Numbers auto-complete kicked in beautifully.

I then returned to the Summary sheet to see whether the cumulative total (sum) feature was working properly. Yes.


I'm now left a bit confused by your following instructions regarding pop-up menu, as that's exactly what I had done originally. It worked, but each time I set my cursor in a Category column cell, text entry seemed blocked: no characters appeared for any given keystroke; instead, the control down-arrow appeared to the right of the cell, and clicking on it launched the pop-up menu. This left me thinking it's an either-or proposition: to enter a category name for any transaction, I had to use the pop-up menu, since text entry was blocked. Now that I have deleted the pop-up menu, which is cumbersome compared to auto-complete, I'm happy with the prospect of continuing in this way (without pop-up, for which I see no need now). So if it's really either-or, I'll keep the auto-complete rather than the pop-up to select categories.


But your suggestion and instructions for creating pop-up menu in the Categories column leaves me thinking maybe one can have both up and active, as needed. But I'm not seeing any need... I include this observation because I'm the type of person who likes to understand and learn, so if you care to add further illumination, I will welcome it. Otherwise, please accept my thanks for your ongoing care and generosity of spirit. Your guidance has been clear, quick, and very much appreciated. Marc

Auto-fill Category name when entering a transaction?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.