Hello all,
I have a following task at hand that I haven't been able to figure out how to solve. We always give our clients tiered pricing. The maximum is 5 different prices for different quantities. I created the table in sheet Part Database that includes part number, description of the part, qty1, price1, qty2, price2, qty3, price3, qty4, price4, qty5, price5. On the separate sheet named Sales Order I have an invoice that needs to be filled out. A14 contains my first part on the invoice, B14 is the part description, D14 is the quantity ordered. I need to write a formula in cell E14 to find a correct price from the table in Part Database sheet based on the cell A14 and D14. So, it should look at cell A14 and compare all the parts in the data base, then look at cell D14 and output the price that falls between correct qty range.
Can anyone, please, help me figure out if this is possible?
Thank you in advance!
Oksana
I have a following task at hand that I haven't been able to figure out how to solve. We always give our clients tiered pricing. The maximum is 5 different prices for different quantities. I created the table in sheet Part Database that includes part number, description of the part, qty1, price1, qty2, price2, qty3, price3, qty4, price4, qty5, price5. On the separate sheet named Sales Order I have an invoice that needs to be filled out. A14 contains my first part on the invoice, B14 is the part description, D14 is the quantity ordered. I need to write a formula in cell E14 to find a correct price from the table in Part Database sheet based on the cell A14 and D14. So, it should look at cell A14 and compare all the parts in the data base, then look at cell D14 and output the price that falls between correct qty range.
Can anyone, please, help me figure out if this is possible?
Thank you in advance!
Oksana