Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Please! - Creating Function Calculated Fields within Pivot Tables | Excel Worksheet Functions | |||
Creating tables | Charts and Charting in Excel | |||
Creating a chart from two tables of Times | Excel Discussion (Misc queries) | |||
Creating League tables | Excel Worksheet Functions | |||
Creating a line graph from 2 pivot tables? | Charts and Charting in Excel |