How can you have values depend on a drop down list/menu on excel?

I recently set up a spreadsheet that has many different types of electrical cables (control, power, communication) and their sizes and conductors. I already ha开发者_Python百科ve a bunch of technical data and with it I created a very simple drop down menu where you can select the type of electrical cable.

What I'm trying to do is having a "price" column that would change depending on the type of cable selected. This drop down menu is at the top of the spreadsheet; therefore when the type changes, all prices change for all the cables.

Also if someone knows if this can be done while having different factors for each cable size that would be great (i.e. a cable of 3 conductors would have the price multiplied by 3, a cable of 9 conductors by 9 and so on).

Hope anyone can help me with this!

You can create a combo box (under the Developer ribbon). From there right mouse click on it and select "format control". Input range = your list of electrical cables. I am assuming you have a list of Cables with Price and the technical aspects, insert a column before your list and fill in with 1 through x (x being the last row of data, will use this later on) . You then put in a cell reference for the "cell link". This returns which row the drop down is returning. You then can do a vlookup up of the "cell link" result on the 1 through x to return just that Cable's data. this is somewhat complex, but I can send a mock up if necessary.