Drop-down menu

Numbers vocabulary for this feature is a "Pop-up Menu". It's available as a Cell Format.

See "Using a Checkbox, Slider, Stepper, or Pop-Up Menu in Table Cells" starting on page 96 of the Numbers '09 User Guide.

This guide, and the equally useful iWork Formulas and Functions User Guide are available for download through the Help menu in Numbers.

Regards,
Barry

Excel's data-validation "drop down" feature has one big thing over Numbers' popup cell format: In Excel they are easily edited and sorted because the individual items are in a table. In Numbers, the popup items are part of the cell format. Editing the list is limited and sorting is nonexistent (well, not completely nonexistent, it is a manual process of additions and deletions).

There is an elegant workaround. I didn't design it (I think the credit goes to Jerrold Green in this forum). I'd point you to it, but I'm no longer able to locate it myself. So here's the shorthand version.

Create a table of one column, no headers/footers. A1 through A?? is your dynamic list. (The length will need to be a constant, however.) Let's call this table "DynamicList". I listed the entries as
Anna
Brian
Carol
David
Eve
Fred
Gwen
Howard

Now create a 2nd table, "ListDisplay". No headers/footers. 2 cells (I've made mine 1 row, 2 columns. Format A1 to be a stepper or slider (your choice - this is only about appearance) from 1 to the length of "Dynamic Table" ... in our case, 8. Now make B1 to the formula =OFFSET(DynamicList :: A1,A1-1,0)

Now as you slide or step through the numbers in A1 of ListDisplay, B1 shows the appropriate value. Edit the values in DynamicList and they will show in ListDisplay as well.

There are ways to fine tune this for your needs. If you need your Dynamic List to be horizontal, your offset formula will become =OFFSET(DynamicList ::A1,0, A1-1). If your Dynamic List values are spread out over different cells, a CHOOSE formula will accomplish the same goals.

I hope this helps.

Vince

That is a good workaround but with a few drawbacks:

If you do not have a-priori knowledge of what is in the list, you may have to scroll through every item before settling on what you want. For instance I may have a list of colors for describing something. In that list may be Burgundy, Maroon, and Dark Red. Without first scrolling to see all three of them so you can choose between them, you might pick a less accurate color. With a pop-up you would see them all at the same time and more easily and quickly pick the best one.

For longer lists, a scroll bar is hard to control without skipping over numbers (and therefore skipping over items in your list). A stepper is too slow for long (and not so long) lists.

Still, it is a valid workaround.

vjdjr wrote:
There is an elegant workaround. I didn't design it (I think the credit goes to Jerrold Green in this forum). I'd point you to it, but I'm no longer able to locate it myself. So here's the shorthand version.

Hi Vince,

Here's one of Jerry's posts on the topic.

http://discussions.apple.com/thread.jspa?messageID=11633856

I used 'slider' as the search term and limited the search to posts within 'this year' by 'Jerrold Green1'.

Regards,
Barry

Yep, Barry, that's what I was hunting for. I tried "dynamic slider" and was apparently too specific. Thanks.

Vince

vjdjr wrote:
I tried "dynamic slider" and was apparently too specific. Thanks.

I usually start with the noun(s), and add adjectives only if the search needs narrowing.

Regards,
Barry