Referencing another Excel Sheet and adjusting cells based off entered quantity

Hello

I am currently attempting to edit an existing Excel spreedsheet/formula to be more intuitive. We currently have a Quote sheet template that is copied and used to quote customers. This quote sheet references a Part number table, which is another spreedsheet shared on the network with one sheet. The information in the sheet has the part number, a description, a supplier, and pricing, which we have moved onto the quote sheet. The pricing for individual parts becomes cheaper as more are ordered at once, thus the lower prices for higher quantities. The part number table looks like this:

Part Number Table.png

There are currently no formulas in the part number table. It is just a large excel table.

When in the Quote sheet, entering a part number that is present in the part number table sheet will then populate the entire row in the quote sheet. Here is an example of what the quote sheet looks like. I only entered a quantity and a part number into the quote sheet and the rest was pulled from the table.

Quote Sheet example.png

The quote sheet has a formula written into each cell on each row columns C through O. The formula is as follows (Taken from row 14 column C description cell):

=IF(ISNA(VLOOKUP(B14,'[Part Number Table.xls]Sheet1'!$A:$M,2,FALSE)),"",(VLOOKUP(B14,'[Part Number Table.xls]Sheet1'!$A:$M,2,FALSE)))

The issue that I am having is when entering a part from the table that has a large quantity in the assembly, the pricing is not adaptive and will still have the price as if a lower quantity is being ordered. This is because it is just taking that row from the part number table and adding it to the quote sheet at the appropriate spot. Below are two examples. The first image has an quantity of 1, while the second has a quantity of 25.

Quantity of 1.png

Quantity of 25.png

The total parts cost for 1 assembly then should be $62.50 and not $112.50 since 25 parts are being ordered to make the one product and the price for each would then be lower ($2.50 each for orders of 25). If 25 products where being made using one part each this would work just fine, and many times it does, but certain assemblies using multiples of one part to make one complete product. What I'd like is for the lower pricing to shift to every cell to the left that part qualifies for. So in this case the 1, 2-4, 5-9, and 10-24 column would also be updated to $2.50. But only for the row where the quantity is appropriate.

I attempted to add some extra instruction to the formula such as another LOOKUP, and then trying to compare the column A number (QTY) and setting a cell, but I am thinking that I need to go about pulling the information in a different way since it is currently pulling that entire row into the sheet.

I appreciate any advice or helpful links, and I can add clarification where needed. I apologize if this post is extremely long for a question, but I thought it might be helpful to add as many details as possible.

Thank you.

-Ricky

Thanks for the quick reply! The attached workbook was very helpful!

Would you have any suggestions on how to implement something similar to my current workbooks? The example provided looks at one row correct? The part number table currently works by adding that number anywhere in the sheet it will be pulled. Would I be able to have one row of break points that each row in the part number table would compare to?

Thanks again. I appreciate the help. I'm new with Excel so anything and everything helps a ton!

-Ricky

So by this method I would need to use a break-point row for each part number and its special pricing? In your example it seems like I am unable to change where it is referencing to. This is because you have an array correct? Is there a way to reference each row to one break-point table? This would be helpful since every part has the same break-points.

Thanks again!

-Ricky