3 Replies Latest reply: Jan 30, 2013 1:57 AM by Barry
danielfromcentral falls Level 1 Level 1 (0 points)

I have a rather confusing question about what in my head seems to be  "conditional" pop-up menu formatting. If there even is such a thing?  Let me set the stage…  I have a table with a "state" column where each cell is formatted with a pop-up menu of 3 state choices - FL, MA or RI.  Is there any way to create a situation where the next cell to the right would have a "choice" of pop-up menu formatting with territories based on the result of the "state" column (again FL, MA, RI) to the left.  So if the state = RI, I would be able to set up conditions in the next cell over that would let me select from a pop-up menu of RI based territories that I've assigned previously, or if the "state" = MA, i would be able to select from another pop-up menu of MA based territories? 

 

I know how to use the pop-up menu formatting BTW. 

 

I've looked through the formulas list but being Numbers-stupid I have no clue where to start.  I did look through but didn't see anything that seemed remotely similar.  I also apologize for my terrible description of the issue. It sounded reasonable in my head but just looks like gibberish here. 

 

Thanks for any help at all! 

 

Dan


Numbers _09, OS X Mountain Lion (10.8.2)
  • 1. Re: "Conditional" Pop-up cell formatting
    Wayne Contello Level 6 Level 6 (13,615 points)

    You want dynamic popups (which change content based on the selection in another cell).  Numbers does not support this.  There are other ways but they are generally very dependent on how you want to work.

  • 2. Re: "Conditional" Pop-up cell formatting
    danielfromcentral falls Level 1 Level 1 (0 points)

    Oh man Wayne thanks!  I know it didn't "solve" my question but at least I know I'm not overlooking something! It's really not going to be a big deal to just list all the choices in the "territories" cell, there's only like 10 or 12 to choose from. 

     

    Thanks again,

    Dan

  • 3. Re: "Conditional" Pop-up cell formatting
    Barry Level 7 Level 7 (29,180 points)

    Hi Dan,

     

    With only a dozen territories to choose from, putting them directly int the pop-up menu seems a reasonable choice. Having done so, you could make the state appear automatically, using a LOOKUP table, with the territory names as the search key. Here's an example:

    Picture 26.png

     

    Column A on Main contains a series of pop-up menu cells, all with the same menu items.

    Column A on Data contains the same items (Territory 'names') as the menus. Order doesn't matter, but 'spelling' must be an exact match.

     

    There is a single formula on Main, entered in B2 and filled down and right from there. Nmbers automatically edits the formula to fit its new location. Manual editing is required in D2 before filling down, as I wanted values from column H of Data here, not from column D.

     

    B2: =LOOKUP($A,Data :: $A,Data :: B)

     

    Fill right to D2. Edit D2 to read: =LOOKUP($A,Data :: $A,Data :: H)

     

    Select the three formulas and fill down to row 10.

     

    Choose a territory in column A, and the information for that territory will fill in the rest of the row.

     

    Regards,

    Barry