creating a quote looking up from tables
 

I am trying to create a quote system using excel, and need to be able to
select from
a range of quantities and then
select from a range of colours

I have tried using index and match functions but this will only work if
the quantites are the same as in the table.but i need to be able to
select from a range of quantities
0-15
16-24 etc

I cant use nested if statements as there are 12 quantity groups

thanks in advance


--
andyell
------------------------------------------------------------------------
andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
View this thread: http://www.excelforum.com/showthread...hreadid=550820

creating a quote looking up from tables
 

One play ..

A sample construct is available at:
http://www.savefile.com/files/5747910
Quote System 2 variables Tiered Qty by Colour.xls

Assume the reference price table below is in sheet: X, wihin A1:D4

Qty red blue green
1 $10.00 $20.00 $15.00
16 $20.00 $50.00 $35.00
25 $30.00 $60.00 $50.00

and in another sheet: Y (say)
the quantities and colours
will be input / selected in A2 and B2 down

Qty Colour Quote
12 Green
25 Red
5 Blue
17 Red
etc

Put in C2, then copy down:
=IF(OR(A2="",B2=""),"",INDEX(X!$A$1:$D$4,MATCH(A2, X!A:A,1),MATCH(B2,X!$1:$1,0)))

Format col C as currency

Col C will retrieve the quotes from the price table in X

Qty Colour Quote
12 Green $15.00
25 Red $30.00
5 Blue $20.00
17 Red $20.00
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andyell" wrote:

I am trying to create a quote system using excel, and need to be able to
select from
a range of quantities and then
select from a range of colours

I have tried using index and match functions but this will only work if
the quantites are the same as in the table.but i need to be able to
select from a range of quantities
0-15
16-24 etc

I cant use nested if statements as there are 12 quantity groups

thanks in advance


--
andyell
------------------------------------------------------------------------
andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
View this thread: http://www.excelforum.com/showthread...hreadid=550820