Need help with List Price Quote with different possibilities

I need to create a spreadsheet for estimates for kitchen cabinets. There are different cabinets styles, different items and different list prices depending on both.

I have a drop down menu to choose the cabinet style
I have a drop down menu to choose the item
I have a catalog spreadsheet with all the styles and items and their respective list prices

Items StyleA StyleB StyleC ...
3012 208 219 229
3015 218 229 240
3018 242 255 266
...

1- I choose the style.
2- I choose the cabinets for the particular kitchen I'm working on.
I need excel to enter the list price for that item in the style chosen from the drop down menu.
I need excel to enter the description of that item as well.

Please see simple example attached.

Thank you.

Put this formula in B5 of your Estimate sheet:

=IF(A5="","",INDEX(Catalog!A:A,MATCH($A5,Catalog!$B:$B,0)))

and this one in D5:

=IF(A5="","",INDEX(Catalog!$C:$J,MATCH(A5,Catalog!$B:$B,0),MATCH($B$2,STYLE,0)))

Then copy the formulae down as far as you need to.

Hope this helps.

Pete

How can your select different colors? For Example for W3012B the number that appears in D5 on the estimate sheet is only for the black. Is there away to select through the other color prices?

Thank you

You can select the colour using the drop-down in cell B2. With it set to Black, and with W3012B in A5 you will see a price of $208, but if you choose Red in B2 the price will change to $219. Choose yellow in B2 and it will change again to $229, and so on.

Hope this helps.

Pete

Hello karajordan, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Pete, in the scenario that you would need one cabinet black and one red, how would you be able to pick a different color at the same time? As the way it is set up it only allows you to choose one at a time.

Thank you

Well, that was based on what the OP wanted/described in the first post. I suppose if you are having a kitchen installed you would want all the units to be the same colour.

If you want to take this further and have the facility of different colours for each item, then you need to read Cutter's post #5, and start a new thread related to your particular circumstances.

Pete