functions/events based on user selections

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I am attempting to create a scenario worksheet for users
to review pricing data based on answers to a few questions.
For example:
1. Online transactions?
If yes, the similar questions as below are posed, but with
different pricing.
If no, show question 2.

2. Ticket issued?
If yes, question 3.
If no, price ($5).

3. US travel or intl.?
If US, price =$10 and next question.
If intl., price =$50 and next question.

4. Pick up or delivery of ticket?
If pick up, no additional cost.
If delivery, add another $10.

Sum based on user's choices.

HOW DO I DO THIS IN EXCEL 2000? I have created the user's
choices to questions in a cell dropdown list. I can't
figure out how to show certain questions based on previous
question's answer. I attempted VLOOKUP, but can't get the
results I want. Am I going about this the wrong way?
 
One way:

Make a lookup table, Say J1:L4, called LookupTable:

J K L

1 Pricing Online Offline
2 Issued 5 10
3 US 10 20
3 Intl 50 100
4 Delivery 10 20


Then in

A B
1 Online? <"Online"/"Offline" dropdown>
2 Issued? <"yes"/"no" dropdown>
3 US/Intl? <"US"/"Intl" dropdown>
4 Pickup? <"Pickup"/"Delivery" dropdown>

Then the total cost is

=(B2="Yes") * (VLOOKUP("Issued",LookupTable,2+(B1="Offline"),FALSE)) +
VLOOKUP(B3,LookupTable,2+(B1="Offline"),FALSE) + (B4="Delivery") *
VLOOKUP("Delivery",LookupTable,2+(B1="Offline"),FALSE)
 
Back
Top