Combine formula with pop-up menu in spreadsheet

Question

Level 1

4 points

Combine formula with pop-up menu in spreadsheet

Hi, I have a budgeting tool where I am able to extract a csv file for the bank and load the date, particulars and amount into my spreadsheet. Each transaction is categorised manually with a pop-up to improve data consistency e.g. Food, Electricity, Gas, etc etc.

To make things easier I was wondering if it is possible to include a formula as the default entry. this formula would then lookup a table for the likes of Woolworths and return Food. If no result is found, it would return a blank, but the user still has the option to click on the cell and manually select from the pop up menu?

Is such a thing possible, or will the result of the formula make the invalidate the pop-up menu, resulting in an error?

I have tried typing the formula into the pop-up menu, but as I expected, it simply printed the formula as text, but did not recognise it as a function.

Is there perhaps a better way of doing this, without having to check several cells?

MacBook Pro 13″, macOS 12.6

Posted on Nov 27, 2025 12:22 AM

Reply

Answer 1

Camelot

Level 9

60,981 points

Nov 28, 2025 2:41 PM in response to RichardINZ

You can't do quite what you're asking.

Cells can have formulas, or they can be pop-up menus. They can't have both.

Unfortunately, when you set a cell's format to one of the dynamic types (checkbox, popup menu, etc.) you lose the underlying formula.

Without more details, the closest I could think of is a two-part....

First, a column that uses a XLOOKUP() based formula that looks up the vendor against a table of vendors, and returns the corresponding category, and returns an empty string for non-matching items.

THEN, you select the cells in this column and set their Cell format to Pop-up Menu. Numbers will create a pop-up menu using the existing values in the selection, which will match your categories. Now it should be clear which items don't have a category, with a pop-up menu of items to select from.

There's no way to do this dynamically, though. Either the cell value is based on the lookup, or it's based on a selection from the menu (i.e. if you change the vendor name, or the values in the categories table, this cell won't reflect that change).

Reply

Answer 2

RichardINZ Author

Level 1

4 points

Nov 28, 2025 5:07 PM in response to Camelot

Hi Camelot, Thank you for your response. I suspected that this was going to be the response, though I did not know that Numbers can create the pop-up list from the pre-populated cells. That is useful to know!!! My use of pop-up menus in the past has always been to populate them in Numbers, though in Excel I have dynamically created them. Nice to know that this can be done in Numbers. Either way I have never been able to assign functions to a pop-up menu, which is ultimately what I was hoping to achieve, but I will revert to doing it the longer way.

Thank you, again.

Reply

Combine formula with pop-up menu in spreadsheet

Similar questions