phelectric
iPF Noob
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?
Numbers Pick List?
- Thread starter phelectric
- Start date
petermillard
iPF Noob
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
=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
iPad Fan
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.
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:
petermillard
iPF Noob
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
iPad Fan
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.
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
iPF Noob
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
Pete
petermillard
iPF Noob
Yep. Result!
petermillard
iPF Noob
Don't know if we helped the OP at all, but it was fun to noodle around with!Cheers, Pete
TracyMacleoda
iPF Noob
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
