Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Guest list Calculator formula

Hello.

What formula would I use to populate Column H (Price) with a value option, by changing Column F (Replied) and depending on the age group selected in Column G?

I have looked at "IF" "AND" and I am trying to understand if i can use "INDIRECT" but honestly don't know how so I am turning to you guys to ask for help?

MacBook Pro (Retina, 13-inch,Early 2015), macOS High Sierra (10.13.1)

Posted on

Question marked as Solved

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

Posted on

Question marked as Helpful

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

There’s more to the conversation

Question marked as Solved

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

Nov 12, 2017 5:34 PM

Question marked as Helpful

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

Nov 12, 2017 5:34 PM

Thanks Wayne,

This solved my problem, I hid the Pricelist table on a separate sheet with other quotes and expenses and it worked a treat.

Al.

Nov 12, 2017 5:50 PM

Thanks Barry.

There should be something entered in column F for each guest, so, as you say the error message serves a useful purpose. (the None value was because I had it set to "start with blank" in the popup menu, I guess changing that to "start with first item" would remove that issue) 🙂

Al.

Nov 12, 2017 5:57 PM

User profile for user: Urbane Al

Question: Guest list Calculator formula