Numbers budget Transactions sheet not retaining Pop-up categories

Question

Level 1

4 points

Numbers budget Transactions sheet not retaining Pop-up categories

Converting to Numbers from iBank. Trying to get the Transactions sheet column C to auto-fill, if that feature is available, or to remember and offer my complete Categories list as a drop-down (Pop-Up?) menu.

I successfully edited Category names on Summary by Category sheet. Then I opened Transactions, deleted all sample entries, and began "test driving" by making entries based on my own recent spending. When I place my cursor in the Category cell and begin typing, no text is entered; it behaves as frozen. So I tried Format > Cell > Data Format > Pop-Up Menu and began using the +/- buttons to edit Category names, replacing all default items with my own, exactly copying each name from the Summary by Category sheet. It looked fine, but when I exit Data Format, moving my cursor to the Transactions sheet to make a new entry, hoping and expecting to be able to employ the Category names just loaded into Pop-Up Menu, again I experience freeze-out at the Category cell: Date and Description accept text, but Category does not allow text entry, nor does the small "down arrow" icon furnish the Pop-Up Menu of categories I created.

Maybe I stumbled onto the beginnings of a solution: when all else seems to have failed, I clicked on the column heading "C" to select the entire column, then selected "Merge Menu Items" up in the sidebar Data Format section. Instantly the window below Pop-Up Menu is populated correctly with all of my remembered category names. But I still can't begin typing and get auto-fill for a category at this point; but if I hit the Space Bar on the keyboard instead of an alpha key, up comes a pop-up menu allowing me to select a category, which is auto-entered as needed. I am left with the impression that I'm still doing something wrong, and there must be a simpler, more reliable/consistent way to enter Category names for each new transaction without a bunch of column-selecting and menu.

Any guidance? Advance thanks for the generosity of spirit and skill given here.

iMac 21.5″, macOS 10.13

Posted on Nov 30, 2020 10:15 AM

Reply

Answer 1

Best reply

Barry

Level 9

59,867 points

Nov 30, 2020 1:10 PM in response to mjanes88

HI mjanes,

The easiest way to construct a set of popup menu cells is to start on the Summary by Category table.

Replace the names of the categories on that table with the category names you want to use, one per cell, adding rows if you need to.

When you have all the categories listed in that column, select all of the cells containing the category names.

Open the Format Inspector (by clicking the format brush), choose Cell, then set the Data format to 'Pop-up menu.

Set the 'Start with' control to "Blank"

Each of the selected cells will now contain a popup menu containing 'none' as its first item, followed by all of the categories listed in the selected cells, and set to show the value that was in the cell at the beginning.

Now select one of the cells, then use the control to the right of the cell to set that one to 'none' (which will display as an empty cell). With the cell still selected, press command-C to copy it to the Clipboard, then reset the menu to show the original category value.

On the transaction table, select the cells in which you want to have the popup menu, then press command-V to paste the a copy of the menu, set to none, in each of the cells.

Selecting the category for each transaction is then a matter of selecting the cell, clicking the control, and selecting the category from the popup menu.

As all categories will be selected from menus containing spelling that matches the categories in the Summary table, no spelling errors in entering them will cause a transaction to not be included in the summary amounts.

Autofill/Autocompletion:

This is an automatic feature that is always active in Numbers.

The feature builds and maintains a virtual list of every entry made in it's column, and matches those to new entries in the column as they are typed, producing a list of all entries matching what's been typed so far, and trimming that list as each new character is entered in the cell. The user can pick any listed item, which will then be entered into the cell completing the partial entry to that point.

The list of 'all' entries includes, of course, any errors made in previous entries. These can be removed only by correcting the error in the cell where it occurred.

As all entries are based on a previous entry, which was entered from the keyboard, there's no guarantee that the category entries will match values on the summary table. An early error could lead to several entries missing from the summary calculations for that category. Probably not a large danger, but a possibility to be aware of.

Regards,

Barry

Reply

Answer 2

mjanes88 Author

Level 1

4 points

Nov 30, 2020 2:07 PM in response to Barry

Barry,

Please accept my heartfelt thanks for your clear and efficient description of steps. They worked perfectly (as I suspect you knew they would.) I consider myself relatively tech-savvy and have had ample spreadsheet experience, but the particular technique for using the Pop-Up feature, cited in vague terms by others, became focused and clear in your skillful hands. You're an able teacher. Thank you.

I see that auto-fill works in the Description field but not the Categories column, which requires use of the "controller" to launch the Pop-Up menu. I'm fine with that.

I now feel well prepared to enter a new budget year equipped with tracking tools. The only thing Numbers appears not to offer, which iBank and others do, is the ability to reconcile one's account records with bank statements; I logged checking account and credit card account separately, reconciled them separately, all within iBank. Should be relatively simple to go back to the analogue approach to that.

Again, my thanks. You are my hero!

Reply

Numbers budget Transactions sheet not retaining Pop-up categories

Similar questions