Dropdown menus in Google Sheets - Real Business Scenario

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.

Sample Data

Product	Price	Stock
Notebook	2.50	150
Pen	1.20	300
Stapler	5.00	75
Highlighter	1.80	200
Folder	3.00	120
Paper Clips	0.50	500
Envelope	0.80	400
Marker	2.00	180

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.