I am trying to create an order form where column A has a drop down box to
choose an item and column B has a drop down box to choose a category.
Depending on the choices selected I need to create variable prices.

ie if A = jumper, B = wool then C = £3, but if A = shirt, B = silk then C = £5

Can anyone help me please

=IF(AND(A2="jumper",B2="wool"),3, AND(A2="shirt","B2="silk"),5,""))

in C2

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"cerobeth" wrote in message
...
I am trying to create an order form where column A has a drop down box to
choose an item and column B has a drop down box to choose a category.
Depending on the choices selected I need to create variable prices.

ie if A = jumper, B = wool then C = £3, but if A = shirt, B = silk then C
= £5

Can anyone help me please

You could use data validation for the drop down boxes..

See Here.

http://www.contextures.com/xlDataVal01.html

Then the VLOOKUP function for the prices.

for example your information is contained Sheet2 A2:C50
A B C
1 Clothing type Material Cost
2 Jumper Wool £3
3 Shirt Silk £5


Your formula might look like this (assuming your drop down lists are in
Sheet1
cell A2 (Clothing type) Cell B2 (Material)
In Sheet1 cell C2 type...

=VLOOKUP(A2,Sheet2!A2:C50,3,0)

This will return the following...

If Jumper is selected from the first drop down list cell C2 will show £3.

Hope this helps,

Gav.


"cerobeth" wrote:

I am trying to create an order form where column A has a drop down box to
choose an item and column B has a drop down box to choose a category.
Depending on the choices selected I need to create variable prices.

ie if A = jumper, B = wool then C = £3, but if A = shirt, B = silk then C = £5

Can anyone help me please

Thanks for your help. You make it sound so easy, but will that work having
so many variations? Column A can be any number of items, column B has
several items but only 1 of these items needs to be recognised in the formula
and C will give a different price for each of items A.

"Gav123" wrote:

You could use data validation for the drop down boxes..

See Here.

http://www.contextures.com/xlDataVal01.html

Then the VLOOKUP function for the prices.

for example your information is contained Sheet2 A2:C50
A B C
1 Clothing type Material Cost
2 Jumper Wool £3
3 Shirt Silk £5


Your formula might look like this (assuming your drop down lists are in
Sheet1
cell A2 (Clothing type) Cell B2 (Material)
In Sheet1 cell C2 type...

=VLOOKUP(A2,Sheet2!A2:C50,3,0)

This will return the following...

If Jumper is selected from the first drop down list cell C2 will show £3.

Hope this helps,

Gav.


"cerobeth" wrote:

I am trying to create an order form where column A has a drop down box to
choose an item and column B has a drop down box to choose a category.
Depending on the choices selected I need to create variable prices.

ie if A = jumper, B = wool then C = £3, but if A = shirt, B = silk then C = £5

Can anyone help me please

With the table of items/materials/Costs in B2:D9 as in:


A
B
C

1
Clothing Type
Material
Cost

2
Jumper
Wool
£3.00

3
Jumper
silk
£5.00

4
Jumper
cotton
£2.00

5
Shirt
Silk
£5.00

6
Shirt
Wool
£4.00

7
Shirt
velvet
£7.00

8
Shirt
cotton
£3.00



=INDEX(C2:C8,SUMPRODUCT((A2:A8=F1)*((B2:B8=G1)*(RO W(A2:A8)-1))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"cerobeth" wrote in message
...

Thanks for your help. You make it sound so easy, but will that work
having
so many variations? Column A can be any number of items, column B has
several items but only 1 of these items needs to be recognised in the
formula
and C will give a different price for each of items A.

"Gav123" wrote:

You could use data validation for the drop down boxes..

See Here.

http://www.contextures.com/xlDataVal01.html

Then the VLOOKUP function for the prices.

for example your information is contained Sheet2 A2:C50
A B C
1 Clothing type Material Cost
2 Jumper Wool £3
3 Shirt Silk £5


Your formula might look like this (assuming your drop down lists are in
Sheet1
cell A2 (Clothing type) Cell B2 (Material)
In Sheet1 cell C2 type...

=VLOOKUP(A2,Sheet2!A2:C50,3,0)

This will return the following...

If Jumper is selected from the first drop down list cell C2 will show £3.

Hope this helps,

Gav.


"cerobeth" wrote:

I am trying to create an order form where column A has a drop down box
to
choose an item and column B has a drop down box to choose a category.
Depending on the choices selected I need to create variable prices.

ie if A = jumper, B = wool then C = £3, but if A = shirt, B = silk then
C = £5

Can anyone help me please

OOPs! I should have check how it looked before I posted:

With the table of items/materials/Costs in B2:D9 as in:

A B C
1 Type Material Cost
2 Jumper Wool
3 Jumper Silk
4 Jumper Cotton
5 Shirt Silk
6 Shirt Wool
7 Shirt Velvet
8 Shirt Cotton
9 Shirt


Then use:
=INDEX(C2:C8,SUMPRODUCT((A2:A8=F1)*((B2:B8=G1)*(RO W(A2:A8)-1))))

where F1 is the Colthing Item and G1 is the Material

Hopefully this will be more understandable

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

I don't understand this formula to be able to use it. maybe my example of
clothing wasn't very good so i'll explain properly

the spreadsheet is for a price list for wedding stationery
Column A is the design
Column B is the items of stationery
each design has a different price for each item

I know its confusing and i'm probably in way over my head but i need to
succeed.


"Sandy Mann" wrote:

OOPs! I should have check how it looked before I posted:

With the table of items/materials/Costs in B2:D9 as in:

A B C
1 Type Material Cost
2 Jumper Wool
3 Jumper Silk
4 Jumper Cotton
5 Shirt Silk
6 Shirt Wool
7 Shirt Velvet
8 Shirt Cotton
9 Shirt


Then use:
=INDEX(C2:C8,SUMPRODUCT((A2:A8=F1)*((B2:B8=G1)*(RO W(A2:A8)-1))))

where F1 is the Colthing Item and G1 is the Material

Hopefully this will be more understandable

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

It doesn't make any difference what you have in the columns the formula will
work anyway. Send me a sample worksheet with your table of
Design/stationery/Cost and I will return it with an example of how to use
the formula.

As it saiys in a signature replace the @mailinator.com because that is a
spam trap.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"cerobeth" wrote in message
...
I don't understand this formula to be able to use it. maybe my example of
clothing wasn't very good so i'll explain properly

the spreadsheet is for a price list for wedding stationery
Column A is the design
Column B is the items of stationery
each design has a different price for each item

I know its confusing and i'm probably in way over my head but i need to
succeed.


"Sandy Mann" wrote:

OOPs! I should have check how it looked before I posted:

With the table of items/materials/Costs in B2:D9 as in:

A B C
1 Type Material Cost
2 Jumper Wool
3 Jumper Silk
4 Jumper Cotton
5 Shirt Silk
6 Shirt Wool
7 Shirt Velvet
8 Shirt Cotton
9 Shirt


Then use:
=INDEX(C2:C8,SUMPRODUCT((A2:A8=F1)*((B2:B8=G1)*(RO W(A2:A8)-1))))

where F1 is the Colthing Item and G1 is the Material

Hopefully this will be more understandable

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk