I'm trying to create a product page that will return a list of products based upon questions that a customer answers (or that the sales rep fills in). Basically I'm trying to return a value based on the answer to 2 questions (pulled from another sheet), which are in B and C, and the result will go into D (# needed).
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]item[/TD]
[TD]question[/TD]
[TD]# of stations[/TD]
[TD]# needed[/TD]
[/TR]
[TR]
[TD]classroom pipets, single[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]professional pipets, single[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]classroom pipets, set of 8[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]professional pipets, set of 8[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]classroom pipets, single[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]professional pipets, single[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]classroom pipets, set of 8[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]professional pipets, set of 8[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
There are 2 scenarios above that should hopefully illustrate what I'm trying to do! The customer can choose between 2 different models - classroom or professional. If they choose classroom, then the professional pipets need to have a quantity of 0. Additionally, if they need more than 7, then the set of 8 model should reflect that - they get 1 quantity of the bulk set, plus the remainder in singles.
In the 2nd scenario, the customer wants professional pipets, but only 5. So no classrooms are selected, and only 5 singles.
I tried doing a lookup table for this to make life easier, but I'm really having trouble wrapping my brain around how to do a VLOOKUP within an IF fxn, with an OR! Or maybe there is an easier solution that I don't know about (very very likely)!?
Thanks in advance!
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]item[/TD]
[TD]question[/TD]
[TD]# of stations[/TD]
[TD]# needed[/TD]
[/TR]
[TR]
[TD]classroom pipets, single[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]professional pipets, single[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]classroom pipets, set of 8[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]professional pipets, set of 8[/TD]
[TD]classroom[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]classroom pipets, single[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]professional pipets, single[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]classroom pipets, set of 8[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]professional pipets, set of 8[/TD]
[TD]professional[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
There are 2 scenarios above that should hopefully illustrate what I'm trying to do! The customer can choose between 2 different models - classroom or professional. If they choose classroom, then the professional pipets need to have a quantity of 0. Additionally, if they need more than 7, then the set of 8 model should reflect that - they get 1 quantity of the bulk set, plus the remainder in singles.
In the 2nd scenario, the customer wants professional pipets, but only 5. So no classrooms are selected, and only 5 singles.
I tried doing a lookup table for this to make life easier, but I'm really having trouble wrapping my brain around how to do a VLOOKUP within an IF fxn, with an OR! Or maybe there is an easier solution that I don't know about (very very likely)!?
Thanks in advance!