Q: How to break down/ avoid massive pop up menu
I'm creating a monthly budget spreadsheet that will condense my daily spendings into a summery table.
I'm trying to figure out a way to avoid an extremely large pop up menu with my detailed categories on it.
I have read and tried the nested pop up menu post (Nested (or Cascading) Pop-Up Menus in Numbers 3) but to the best of my understanding due to the filter only functioning by column, the dependent result will only 'show' in the row below. I'm trying to have it show in the next column.
This is my large list of categories
Housing | Transportation | Insurance | Loans | Food | Entertainment | Personal Care V | Personal Care S |
Rent or Mortgage | Fuel | Home | Student | Groceries | Cinema | V Medical | S Medical |
Electricity | Insurance | Health | Credit card | Dining out | Concerts | V Gym | S Gym |
Gas | RAA | Life | Credit card | Food other | Theatre | V Mobile phone | S Mobile phone |
Water | Maintenance | Insurance other | Credit card |
| Sporting events | V Clothing | S Clothing |
Internet | Taxi |
| Credit card |
| Entertainment other | V Dry cleaning | S Dry cleaning |
Phone | Public transport |
| Loans other |
|
| V Hair/nails | S Hair/nails |
Maintenance or repairs | Transportation other |
|
|
|
| V Other | S Other |
Housing other |
|
|
|
|
|
|
|
I was trying to use the nested pop up menu work around to have one pop up list of the headings in bold, and then showing the subcategory in the next column
like this
Date | Description | Category | Subcategory | Unit cost | Quantity | Total |
Fri, 1 Jul 2016 | The alcove cafe | Food | Dining out | AU$24.80 | 1 | AU$24.80 |
Fri, 1 Jul 2016 | Petrol | Transportation | Fuel | AU$60.51 | 1 | AU$60.51 |
Sun, 3 Jul 2016 | Milk | Food | Groceries | AU$2.50 | 1 | AU$2.50 |
Mon, 4 Jul 2016 | RAA | Transportation | RAA | AU$97.00 | 1 | AU$97.00 |
When i followed the instructions I could only have the subcategory showing in the next row instead:
Food | show |
Groceries | show |
Is there a possible solution?
Any ideas highly appreciated.
MacBook Pro with Retina display
Posted on Jul 4, 2016 4:04 AM
Hi S-n,
Not possible, as the nested pop-ups depend on hiding the ones that don't apply, and columns c only be hidden 'manually.' You can keep everything on a single row using a Pop-up and Slider set, though.
Here's an example, using your Categories table as a lookup table.
Main is your Budget table with an added column (E) holding a set of Stepper cells with values set as 1-8 in increments of 1. You might also try using a Slider instead of a Stepper here.
Column C contains a Pop-up menu with the items listed in Row 1 of the Categories table as the menu items. The menu is set to Start with Blank to allow setting to the state shown in C10.
Column D contains the formula below, entered in D2, and filled down to the end of the column:
D2: =IF(C="","",INDEX(Categories::$A:$H,E+1,MATCH(C,Categories::$1:$1,0)))
The IF part at the beginning ( IF(C="","", ) checks for an entry in the Categories column ( C ), if C is empty, then the formul leaves the cell in column D empty as well.
If there is an entry in the cell on 'this row' of column C, the IF test result is FALSE, and IF passes control to INDEX.
MATCH reads the value in this row of column C, looks for that value in the first row of Categories and returns the number indicating its position in that list (eg 3 for "Insurance").
Index uses 'the value in this row of E' +1, and the value returned by MATCH to determine the row and column of the range of cells it is reading (Categories::$A:$H) from which to get the value to return to the formula (and the cell is which the formula sits).
Cautions: I originally had the Stepper in column D and the Subcategory results in column E, Stepper cells show the control attached to the right edge of the cell when selected, partially obscuring the cell to the right of the stepper (where, with that arrangement, the result was displayed). Centering the text in column E was enough to make the subcategory identifiable when partially obscured, and may be more workable than the arrangement above.
The current arrangement, with the control (Stepper) to the right of the results column leaves the result visible while setting the stepper, but introduces more strongly the temptation to enter the subcategory directly from the keyboard, replacing the formula in that cell.
If you do want to switch the positions of the two columns, you can do so without having to rewrite the formulas. Select the Subcategory column by clicking it's Column Reference tab (currently "D") above the table. With the column selected, click again, just left of the letter on the reference tab, and drag right until the Stepper column jumps to the left. Drop the column being moved into the space opened by the move.
Regards,
Barry
Posted on Jul 4, 2016 2:06 PM
