Guest list Calculator formula

I would no use a pop-up in column H. I would have that fill in automatically based on the selections in columns F and G.

Add a second table for the price list and name the table "PriceList" and set up as shown. The first row is a header row

In the guest list table (on the left)...

H2=IF(F2="YES", VLOOKUP(G2,PriceList::A:B,2,FALSE), "")

this is shorthand for... select cell H2, then type (or copy and paste from here) the formula:

=IF(F2="YES", VLOOKUP(G2,PriceList::A:B,2,FALSE), "")

to fill down, select cell H2, copy

select cells H2 thru the end of column H, paste

Hi Al,

Column G is a pop-up menu on which you choose the age group of each member of the party on receiving the initial request.

Column F contains a three item pop-up: none, No, Yes. (none, in this situation is redundant, unless you want to retain the clean display of an empty cell where no entry has yet been made in the preceding columns).

Column G can show four values: blank, 0, 44.50, or 89.00, but if you use a pop-up menu to do this, the change from the default blank' to any other value must be done manually. Pop-ups (and checkboxes) cannot be controlled by formulas.

For automatic entry in column G, you'll need to use a formula, then either contains the three numerical values, or that grabs the appropriate one from a separate table.

Example:

There's one formula on Data, shown under the tables, entered in G2 and filled down column G to its end.

The formula uses VLOOKUP to get the correct rate from Rates, and IF to suppress that calculation until there is a "Yes" in that row of column E.

Setting column E to "Yes" on a row where there is no choice made in column F will cause an error triangle on that row of column G. While that too could be suppressed, its presence serves as a useful notice that the popup in column E has been set to a value that does not make sense, so I've left it in.

Regards,

Barry