How to insert value based on pop-up menu choice?

Hi plemax,

Format those cells as Currency.

Select all those cells and format them as Pop-Up Menus.

Will that work for you?

Regards,

Ian.

create a second table which is a price list (name the table "Price List" as shown below, on the right):

In the table on the left enter the item (selected from a pop up menu that contains "Item 1", "Item 2", etc)

C1=IFERROR(VLOOKUP(B1, Price List::A:B, 2, 0), "")

this is shorthand for... select cell C1, then type (or copy and paste from here) the fomrula

=IFERROR(VLOOKUP(B1, Price List::A:B, 2, 0), "")

select cell C1, copy

select column C, paste

to change the price of "Item 3", alter the value in column B of the corresponding row for "Item 3"

the syntax for vlookup() is available in the online help by typing an "=" in any cell then you can use the function browser to location details about the function:

the syntax for vlookup() is:

VLOOKUP(search-for, columns-range, return-column, close-match)

so the "2, 0" corresponds to the

return-column is 2 which means return column 2 (see image below showing column 1 and 2 in the lookup table)

close-match is 0 means find an exact match

Hi plemax,

EDIT: Had this open while on another topic, and neglected to check for updates when I got to it and composed the response below. Will leave it as is, as a variation on Wayne's solution.

B

What you need is a lookup table, containing the price list, and pop-up menus in column A of your main table (Table 1) containing all of the item names on the price list.

Table 1 has one formula, entered in B2, then filled down to the bottom of column 2:

B2: =IF(ISBLANK(A2),"",LOOKUP(A2,Price list::A,Price list::B))

Cell A2 is set to "None". This menu item is added to a pop-up menu when you set the menu in the cell format inpector > Pop-up menu to "Start with Blank".

If you want the numbers on Table 1 to display as currency, set the format for those cells to Currency, with two decimal places.

Regards,

Barry

Hi plemax,

Numbers can't reference cells (fields) on external documents, whether these are Numbers docs or others.

If your DB is a single table, you can place it on a separate Sheet in the same document and retrieve infomation in basically the same manner as you are doing with the two column 'database' containing the price information in Wayne's solution (and mine) above.

While you can't protect that table from intentional damage or alteration, you can lessen the danger of accidental damage by having it on a separate sheet and locking it. A locked table can still be read, but can't be moved or written to without first unlocking it.

Regards,

Barry