Sugar_nymph

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

Close

Q: How to break down/ avoid massive pop up menu

  • All replies
  • Helpful answers

  • by Barry,Solvedanswer

    Barry Barry Jul 4, 2016 2:06 PM in response to Sugar_nymph
    Level 7 (32,271 points)
    Jul 4, 2016 2:06 PM in response to Sugar_nymph

    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.

    Screen Shot 2016-07-04 at 1.28.06 PM.png

    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

  • by Sugar_nymph,

    Sugar_nymph Sugar_nymph Jul 17, 2016 10:41 PM in response to Sugar_nymph
    Level 1 (4 points)
    iWork
    Jul 17, 2016 10:41 PM in response to Sugar_nymph

    Hi Barry,

     

    Sorry for such a delayed response but it's been a while since I've been able to get to my spreadsheet. This workaround of your works wonderfully, though I can't say that with my limited numbers skills I understand it, I can however use it! Thanks very much!

     

    Kind regards

     

    S-n

  • by Barry,

    Barry Barry Jul 17, 2016 10:51 PM in response to Sugar_nymph
    Level 7 (32,271 points)
    Jul 17, 2016 10:51 PM in response to Sugar_nymph

    Hi S-n,

     

    You're welcome! Thanks for the checkmark.

     

    Regards,

    Barry