Moving Data with Index & Match for Pricing Sheet

Hi There,

I'm not to familiar with excel and I'm looking to making a new pricing catalog. The issue is that I need to be able to choose different colors and different cabinets and for it to show up on one sheet with the correct pricing.

I have made a template from what I know how to do.Copy of Revised Pricing.xlsx

Which are the columns in the Wall Cabinets sheet that we are meant to get the prices from? Is it those headed WM$, C.Mel$, PFB$ and so on? What are the intermediate columns used for? Can we shift these so that the prices are in contiguous columns?

Pete

Hi Pete,

Those Columns WM$ , C.Mel$ Ect. are our cost so we are adding the hardware and the sheet size which is equaling the columns J3,L3, N3,P3,R3,T3,V3 are choices that need to show under the "color" on the estimate sheet determining the cost.

Most of the Cabinet interiors are usually one color but when there is a glass door we need to have the interior a different finish so this is why we need to have the choice to change the color.

Thank you

Cameron

---------- Post added at 01:35 PM ---------- Previous post was at 01:32 PM ----------

As well any information on the "wall cabinets" page can be moved around.

Okay, I think the attached does what you want. There are several different formulae, which I've copied down to row 15, so you just have to add new items to column A and specify the colour using the drop-down.

Hope this helps.

Pete

Thank you very much! I will add more information to see if I can keep it working properly.

Hi Pete,

I looked over it so quickly that the totals that are coming up are incorrect. I have pre calculated them into the rows so the total just needs to be carried over to the estimate sheet. For example a w2412 is going to be 58$ when it is white melamine and $62.80 when it is colored.

Thank you again for your help

I thought you were saying above that it is the two prices added together. Change the formula in H6 to this:

=IFERROR(INDEX('Wall Cabinets'!$J$4:$V$10,MATCH(A6,Codes,0),MATCH(G6,'Wall Cabinets'!$J$3:$V$3,0)),"")

by deleting the first INDEX expression in the formula which is already there.

You might also want to multiply this by the Quantity column - I wasn't sure if this was a unit cost or a total cost column.

Hope this helps.

Pete

I have attached my revisions Revised Pricing A.xlsx

Everything in grey will be going on the estimate sheet. As the "wall cabinets" work sheet I'm just doing my math on. I'm going to continue this down further is there away that when you select the array that you can select into the 1000's into each row?

I understand the concept of the formula I'm just having trouble making it work properly as I believe I just make one or two wrong selections.

Pete what you had was exactly what I wanted though it just was adding to many price together instead of just taking the one sum that has already been calculated in each color.

Thank you

If someone could write it out in steps I can set it up as well. As I'm just not sure what parts I need to put into the formula.

I've done the necessary in the attached workbook. I've also extended the codes drop-down to pick up the extras you have added, and made this a dynamic named range so if you add any more codes it will automatically adjust. I've also adjusted your merged cells, so that customer name, date etc go into separate cells from the label.

Hope this helps.

Pete