0
0
Google Sheetsspreadsheet~15 mins

Dropdown menus in Google Sheets - Real Business Scenario

Choose your learning style9 modes available
Scenario Mode
👤 Your Role: You are a sales coordinator at a retail company.
📋 Request: Your manager wants a simple order form where sales reps can select product names from a dropdown menu to avoid typing errors.
📊 Data: You have a list of products with their prices and stock quantities.
🎯 Deliverable: Create an order form sheet with a dropdown menu for product selection, and show the price and available stock automatically when a product is selected.
Progress0 / 5 steps
Sample Data
ProductPriceStock
Notebook2.50150
Pen1.20300
Stapler5.0075
Highlighter1.80200
Folder3.00120
Paper Clips0.50500
Envelope0.80400
Marker2.00180
1
Step 1: Create a new sheet named 'Order Form' with columns: Product, Price, Stock.
Manually create headers in cells A1 (Product), B1 (Price), C1 (Stock).
Expected Result
Headers 'Product', 'Price', and 'Stock' appear in the first row.
2
Step 2: Create a dropdown menu in cell A2 of 'Order Form' to select products from the product list.
Select cell A2, then go to Data > Data validation. Set Criteria to 'List from a range' and enter 'Products!A2:A9'. Check 'Show dropdown list in cell' and click Save.
Expected Result
Cell A2 shows a dropdown arrow with product names from the 'Products' sheet.
3
Step 3: Automatically display the price of the selected product in cell B2.
=VLOOKUP(A2, Products!A2:C9, 2, FALSE)
Expected Result
When a product is selected in A2, B2 shows its price (e.g., selecting 'Pen' shows 1.20).
4
Step 4: Automatically display the stock quantity of the selected product in cell C2.
=VLOOKUP(A2, Products!A2:C9, 3, FALSE)
Expected Result
When a product is selected in A2, C2 shows its stock (e.g., selecting 'Pen' shows 300).
5
Step 5: Test the dropdown by selecting different products and verify that price and stock update correctly.
No formula; manually select products from dropdown in A2.
Expected Result
Price and stock cells update automatically to match the selected product.
Final Result
Order Form Sheet

+---------+-------+-------+
| Product | Price | Stock |
+---------+-------+-------+
| Pen     | 1.20  | 300   |
+---------+-------+-------+

(Selecting 'Pen' from dropdown in Product cell shows price 1.20 and stock 300)
Dropdown menus help avoid typing errors by limiting choices.
VLOOKUP can fetch related data automatically based on dropdown selection.
This setup makes order entry faster and more accurate.
Bonus Challenge

Extend the order form to allow entering quantity ordered and calculate total cost automatically.

Show Hint
Add a Quantity column and use a formula like =B2*D2 to calculate total cost.