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

Question:

Question: 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?

User uploaded file

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

Reply
Question marked as Solved
Answer:
Answer:

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.


User uploaded file


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

Nov 12, 2017 5:34 PM in response to Urbane Al In response to Urbane Al

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:

User uploaded file

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

Read all replies

Page content loaded

Question marked as Solved

Nov 12, 2017 5:34 PM in response to Urbane Al In response to Urbane Al

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.


User uploaded file


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

Reply Helpful (1)
Question marked as Helpful

Nov 12, 2017 5:34 PM in response to Urbane Al In response to Urbane Al

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:

User uploaded file

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

Reply Helpful (1)

Nov 12, 2017 5:57 PM in response to Barry In response to Barry

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

Reply Helpful
User profile for user: Urbane Al

Question: Guest list Calculator formula