1. Due to the recent site upgrade, the iPadForums (iPF) App will no longer work. The new iPadForums.net website has been designed from the ground up to work perfectly from any mobile, tablet, or desktop computer using the built in internet browser. Create a shortcut to iPadForums.net on your home screen by following these steps: Create an icon for iPadForums.net

Numbers Pick List?

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

  1. phelectric
    Offline

    phelectric iPF Noob

    Joined:
    Dec 28, 2011
    Messages:
    1
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 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
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,935
    Thanks Received:
    1,807
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,913 / 1
    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
    Offline

    petermillard iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    Trophy Points:
    0
    Location:
    London, UK
    Ratings:
    +12 / 0
    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
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,935
    Thanks Received:
    1,807
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,913 / 1
    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.
    Last edited: Jun 11, 2012
  5. petermillard
    Offline

    petermillard iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    Trophy Points:
    0
    Location:
    London, UK
    Ratings:
    +12 / 0
    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
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,935
    Thanks Received:
    1,807
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,913 / 1
    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
    Offline

    petermillard iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    Trophy Points:
    0
    Location:
    London, UK
    Ratings:
    +12 / 0
    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
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,935
    Thanks Received:
    1,807
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,913 / 1
    Nice.

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

    petermillard iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    Trophy Points:
    0
    Location:
    London, UK
    Ratings:
    +12 / 0
    Yep. Result!
  10. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,935
    Thanks Received:
    1,807
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,913 / 1
    Then I guess I was wrong, and happy to be so. :)
  11. petermillard
    Offline

    petermillard iPad Fan

    Joined:
    Apr 16, 2010
    Messages:
    163
    Thanks Received:
    12
    Trophy Points:
    0
    Location:
    London, UK
    Ratings:
    +12 / 0
    :) Don't know if we helped the OP at all, but it was fun to noodle around with!
    Cheers, Pete
  12. TracyMacleoda
    Offline

    TracyMacleoda iPF Noob

    Joined:
    Jul 1, 2012
    Messages:
    3
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    USA
    Ratings:
    +0 / 0
    hmm nice.... i em new to the forum information here is soo kool i was going throught the some information regarding this hope to grow and share what i have for you ppl! after all sharing is caring :)

Share This Page

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

numbers for ipad drop down list

,
numbers ipad sort column
,
pick list in numbers?
,

picklist in numbers

,
picklist number ipad