Numbers Pick List?

Discussion in 'iWork Forum' started by phelectric, Jun 5, 2012.

  1. phelectric

    phelectric
    Expand Collapse
    iPF Noob

    Joined:
    Dec 28, 2011
    Messages:
    1
    Thanks Received:
    0
    I would like to use Numbers to create a list of items on Sheet A from those "picked" from Sheet B. Can anyone tell me how to do it?
     
  2. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,753
    Thanks Received:
    4,750
    I'm pretty sure it can't be done, at least not neatly. I've explored similar projects before, and the conditional statements in the functions are not powerful/flexible enough.

    But I'll be following the thread, just in case you find something I've overlooked. :)
     
  3. petermillard

    petermillard
    Expand Collapse
    iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    You can do this pretty easily using the 'IF' function e.g. on the second sheet use...

    =IF('checkbox-cell-ref'=TRUE,'item-description-cell-ref',"")

    ...in the first cell, then copy it down as many rows as you need.

    I've thrown together a quick test sheet and it works OK; the part that has me stumped is how you sort the second sheet to take out the blank rows; for some reason sorting the column with the IF functions in doesn't seem to work on my iPad, though it's easily done in Numbers on a Mac. So yes, not an elegant solution - at the moment anyway - but it is do-able ;)

    HTH Pete
     
  4. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,753
    Thanks Received:
    4,750
    As I said, not neat, though I like your word 'elegant' better.

    But if I absolutely needed this, I'd take this approach.

    Instead of using IF to create a list of checked entries, use it and the ROW function to return the row numbers of the checked items. This becomes an in-between table. Hide it somewhere out of site.

    In your final table use the SMALL function. You create a formula for each row that finds the smallest, (then the second smallest, then the third, etc.) from your in-between (hidden) table. Then use that result in an INDIRECT function to call up the contents of the list based on the known column of the initial list (where you checked the items) and the returned number (as the row).

    You'll probably have to embed it in an IF or IFERROR function (not sure which or how) to prevent unused rows from displaying an error or other unwanted information.

    It's complicated, it's messy, and it might not work; but it's my best guess at a usable result.
     
    #4 twerppoet, Jun 11, 2012
    Last edited: Jun 11, 2012
  5. petermillard

    petermillard
    Expand Collapse
    iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    Yes, it does seem incredible that something that sounds like it should be should be relatively simple can require such complex formulae. If Apple could come up with a 'hide the blank rows on this sheet/in this range' function it'd be so much easier ;)
     
  6. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,753
    Thanks Received:
    4,750
    Well, it is a personal grade spreadsheet. It was never meant to be head on competition for Excel; and list manipulation isn't exactly a basic spreadsheet function.

    You can't hid a row or column, but you could change the background and text color to be the same; some dark color that warns people that they should leave those cells alone.
     
  7. petermillard

    petermillard
    Expand Collapse
    iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    I've been over-thinking this: you sort the checkbox column on the pick-list sheet and that (obviously!) puts the picked items in order. 'Sort desdending' puts the picked items at the top of the list, blank columns below. Duh!

    Pete
     
  8. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,753
    Thanks Received:
    4,750
    Nice.

    Does it re-arrange the second list (the picked items only) as well?
     
  9. petermillard

    petermillard
    Expand Collapse
    iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    Yep. Result!
     
  10. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,753
    Thanks Received:
    4,750
    Then I guess I was wrong, and happy to be so. :)
     

Share This Page



Search tags for this page
apple number drop down list
,
apple numbers picking from a range of numbers
,
choose number from list
,
color code drop down list in iworks numbers
,
creating pick list in mac numbers
,
iwork numbers create popup list from other cells
,
iwork numbers drop down list
,
iwork numbers picklist multiple
,

numbers for ipad drop down list

,

picklist in numbers