How to create a drop down list that results from the choice of another drop down list

Numbers won't do that. Some combination of Numbers and Applescript might.

Can you think of anyther way to achieve a similar result? For instance, we could display a list of options in a column, those options determined by a number entered into a cell.

Jerry

Here is a workaround that may satisfy you. I used conditional highlighting to hide the text in the column that was not related to the first choice by turning the text white.

The choices in A popup-" •, color, size"

B popup- "choose color, red color, blue color, green color"

C popup- "choose size, big size, medium size, small size"

Someone could still make a choice of color even if color was not chosen in A. I don't see a way around that.

quinn

If all you need is one set of these selections, you can work something out in a column. You would have pop-ups for Size/Color (cell A2), your color choices (A3), and your size choices (A4). You would filter the table to hide/show the correct set of rows (i.e., the correct second pop-up). The color/size pop-up in A2 would always be visible. If you choose "color" in A2, it would unhide row 3 which has the color choice pop-up. If you choose "size" it would unhide row 4 which has the size choice pop-up. Only one of those two rows would be shown. Neither would be shown until you choose either color or size in A2. The formula to determine the price might get a little complicated.

Just realize that with all this showing and hiding of rows to get the job done, your selection(s) have to be in a column, not across a row. Eachj set of selections takes several rows. You could compact it somewhat by moving the price and quantity to column B. Price could go in B2. Quantity could go in both B3 and B4 and your formula will have to determine which one to use.

Hi AJ,

A table called Lookup with Item number, Description and Price

Invoice Table. Type an Item number in Column A.

Formula in A2 (and Fill Down):

=IFERROR(VLOOKUP(A2,Lookup::A:B,2,0),"")

VLOOKUP does the work by finding a match for the item number in the Lookup table.

IFERROR is wrapped around it to return "" (NULL) if there is no entry under Item in the Invoice.

Type the quantity into Column C

D contains

=IFERROR(VLOOKUP(A2,Lookup::A:C,3,0)×C2,"")

Regards,

Ian.

Hi AJ,

Thanks for the green tick and your reply. Just thinking about the workflow when filling in an invoice. Move the columns around to Item, Quantity, Description, Cost. Then you can type an Item number in A, press the tab key to enter Quantity in B. Fewer mouse/trackpad clicks!

Please call back if you need help with revising the formulas.

Regards,

Ian.