order form

[Abe]

help please....

i am trying to create an order form. normally this would be easy
enough...item price x quanity etc, but there are some other variables.
allow me to explain....

1. let's say we are selling products 1 2 3 etc all the way to 250.

2. let's say that products 1 through 50 only should display options (styles)
of a b c d e f and g, while products 51 -100 should display options (styles)
of h i j k l m etc.

I was hoping the two things above could be in drop down boxes... so..for
example..a customer orders product 22, only the a to g options or styles are
shown.

Now..to complicate things a buit further, each style would have a different
price associated with it.

I was thinking somehow to have the products listed in a drop down box...and
depending on the product selected, would evoke some sort of condition (If
product = 22, display options a-g..where the options or styles are maybe in
an external file?

Does this make sense?

Hope so

Any ideas would be greatly appreciated. And please be gentle...I'm a
newbie...but then you probably know that already by the way I asked

Thaks in advance,
Abe

 

[KC Rippstein]

You can create a "cheat sheet" on Sheet2 to accomplish this. Create your
first "options" list (a, b, c, d, e, f, and g) in cells E1 to E7. Highlight
the list and give it a name (call it OptionsAtoG). Naming a range is
easy...there's a white box right next to the formula bar (it should say
E1)...that's where you can type a name for a range).
Then put the prices for each option in column F right next to the option
that price belongs to.
Repeat this for the next set of options underneath those (so starting in E8,
start your next list and then highlight E8 to E13 and give it a name, then
enter prices, and so on).
You can keep repeating that process until all of your unique option
combinations are entered. Then highlight the whole options list with prices
and give it a name (so E1:Fwhatever and use a name like OptionsLookup).
Last, you need to enter your inventory list. In A1 enter 1, in A2 enter 2,
and then highlight A1:A2 and use the fill handle to drag that numerical
series down 250 rows. In column B, enter a description of what that
"product ID" in column A represents, and in column C enter it's unit price
(without options). Then highlight A1:A250 and give it a name
(InventoryList) and highlight A1:C250 and give it a name (InventoryLookup).
Now on your order form, if the "Product ID" is selected in cell B2, B3, B4,
etc., then highlight all of those cells down as far as you need (B20?) and
access the menu for Data->Validation... In the drop down, select "List" and
in the formula type =InventoryList
Now you can go to C2:C20 and type this to get the product description
=vlookup(B2,InventoryLookup,2,0) and hit ctrl+enter
Then in D220 base price is
=vlookup(B2,InventoryLookup,3,0) and hit ctrl+enter
Then in E2:E20 go to Data->Validation and select "List" and use the formula
=if(b2<=50,OptionsAtoG,if(b2<=100,OptionsHtoM,""))
Obviously you can keep going with the if formula until you finish your
options...the last if will end with that double quote. This tells column E
to only show options A to G if the product ID is 1-50, H to M if the product
ID is 51-100, etc.
Now in F2:F20 is your options price. =vlookup(D2,OptionsLookup,2,0) and hit
ctrl+enter
Finally in G2:G20 type =D2+F2 and hit ctrl+enter and in G21 type alt and the
equals sign and hit enter
You're done!
- KC

 

[Roger Govier]

see responses to your question posted in excel.misc