Numbers Pick List?

[phelectric]

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?

 

[twerppoet]

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.

 

[petermillard]

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

 

[twerppoet]

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.

 

[petermillard]

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

 

[twerppoet]

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.

 

[petermillard]

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

 

[twerppoet]

Nice.

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

 

[petermillard]

Nice.

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

Yep. Result!

 

[twerppoet]

Yep. Result!

Then I guess I was wrong, and happy to be so.

 

[petermillard]

Don't know if we helped the OP at all, but it was fun to noodle around with!
Cheers, Pete

 

[TracyMacleoda]

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