What's new
Apple iPad Forum 🍎

Welcome to the Apple iPad Forum, your one stop source for all things iPad. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

Numbers Pick List?

phelectric

iPF Noob
Joined
Dec 28, 2011
Messages
1
Reaction score
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?
 
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. :)
 
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
 
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:
It's complicated, it's messy, and it might not work; but it's my best guess at a usable result.

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 ;)
 
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.
 
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
 
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 :)
 

Most reactions

Latest posts

Back
Top