I have created a drop down list, however, when an option from the list is selected i need to know the formula to add a price depending on what has been selected.
Please help!!
Hi fewie1980,
Try this. The Pop-Up Menu is in the Order Form table, Column A, and the prices are in the Lookup table:
Formula in the table Order Form B2:
XLOOKUP(A2,Lookup::A,Lookup::B,"")
Fill down.
See the Function Browser for XLOOKUP:
Please call back with questions.
Regards,
Ian.
Hi, Thank you for your response, however I am still struggling to get this to work so I will try explain what i am trying to do.
On the calculation tab I want my customer to select the type of property of property and then the cost of that property is then automatically entered in to C3.
I hope this makes sense?
Hi fewie1980,
The options are:
The other criteria (KITCHEN, HALLWAY, LIVING AREA, APPLIANCES) are always the same.
Perhaps a modification to the approach?
The options for a customer to choose are here and we format one column at a time to Pop-Up Menu. Do not include Row 1 (the Header row) or blank cells in the selection.
Now we no longer need the content of Rows 3 and below.
Insert Columns D and E
View behind the curtain. Perhaps the result in Column E could be the string to look up in your price list.
After we hide column D and reset columns A, B and C, this is what the customer can use to choose.
Regards,
Ian.
Hi Ian
I have amended the form as per your suggestion, however how can I now pull through the prices?
Thanks
Scott
Hi Scott,
Good timing!
I am working through how to pull from the price list.
I suggest that the price list contains all options, something like this:
I am copying prices from your screen shot (shown on the right) into the Price List table on the left.
(The Screen shot is temporary, to allow me to type prices into the table. It does not feature in the calculation).
Then we can use XLOOKUP to match a the customer's choice in the Calculation Sheet
Thank you for an interesting question. I am enjoying the challenge!
Regards,
Ian.
Oops, I missed out the Studio Flat Clean. Let's deal with that later.
Hi Scott,
Try this:
Formula in F2 and fill down:
XLOOKUP(E2,Price List::A,Price List::B,"Not Found",0)
Regards,
Ian.
Hi Ian,
I am still struggling to pull the price through
Hi Scott,
Formula in Column E (Table 1 on the Calculation Sheet):
A2&", "&B2&", "&C2&", "&D2
Your screen shot shows a + (plus) sign:
To concatenate (add text strings), use the & operator, not the + operator.
Where did OTHER 1 Bedroom come from? That is a ball from left field 🤔.
Regards,
Ian.
Hi Ian,
I have amended the formula on E3
I am still struggling to get a price in F3 once selections have been made :-(
Hi Scott,
We are chasing each other around the mulberry bush.
Your first screen shot was all upper case with BED BATH. Now I see Bedroom Bathroom and other.
I agree that Bedroom Bathroom will make more sense to your customers 👍, so let's work on that.
Here is my updated document.
The 'Clean Type' sheet, Price List table:
Here is the Calculation sheet, where I have unhidden column D.
D2 is the "Other" (Kitchen, Hallway, Living Area, Appliances) that is always included in a quote.
A2, B2 and C2 are Pop-Up Menus where your customer makes choices:
Formula in E2: A2&", "&B2&", "&C2&", "&D2
Formula in F2: XLOOKUP(E2,Price List::A,Price List::B,"Not Found",0)
The text in Column A of the Price List ('Clean Type' sheet) must exactly match E2 in Table 1 in the 'Calculation' sheet.
Regards,
Ian.
Numbers
