Making drop down menu represent an item & cost

[mark_bates]

Hello.  What I need to do is create a drop down menu that has specific items in it that also reflect a cost. Then be able to take that cost and multiply it by another cell.

Example

Cell A1 is a drop down menu.  I select (Car). Car has rate of $50 associated with it somehow.  In cell B1 I put 10.  In cell C1 the math result of Cells A1 & B1 being multiplied leaving $500.00.  

How can this be done?    

[#2]

@mark_bates

Hi,

You can create a table with each name and its associated value.

Then, in cell C2 use a lookup formula to return the associated value of the name in cell A2 and then multiply it by the value in cell B2.

Hope that helps

[#3]

Thanks @Haytham Amairah on the right track.  What if the table is on another page? also how do you label the table?

[#4]

@mark_bates

You can move the table to a new sheet, and refer to it in the formula.

To change the table name, select any cell of it, then go to Table Design >> Table Name as follows:

Please note that the formula will update automatically with the new name.

Regards

[#5]

@Haytham AmairahOk I got that part.  My Excel didn't have table design in the ribbon for some reason.  If you don't mind could you look at my Excel? On Sheet1, Cell A19 I need the drop down to select equipment from the Equip Page, Cell A1 (equipment description).  I also need for the rate on Equip, Cell B1 to go to Sheet 1, Cell B19.  This would make it so when I select a piece of equipment the rate for the equipment would automatically go to Sheet1, Cell B19.    Thanks for the help.  I have watched countless You Tube Videos and im spinning my wheels.  

[#6]

@mark_bates

Okay, done.

Please find the attached file.

[#7]

Hi Haytham,

I know this was a while ago but you may be able to help.

I've got your above solution working, but what if my columns are not side by side and three are columns in between that I wish to ignore?

Example, I have my description in column B but my costs are in column E