• 100% Satisfaction Guarantee
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now

# I have a drop down menu in cell l1 and I need cell m1 to populate

### Customer Question

I have a drop down menu in cell l1 and I need cell m1 to populate with the corresponding price of the product that is dictated by the choice made from the dropdown in cell l1. How do I do that.
Submitted: 4 years ago.
Category: Microsoft Office
Expert:  jsterq replied 4 years ago.

jsterq :

Hi There,

I will assist you with this today. First, I assume you are using Microsoft Excel...is this correct?

JACUSTOMER-k4l0ocs8- :
JACUSTOMER-k4l0ocs8- :

yes

JACUSTOMER-k4l0ocs8- :

I have to columns of data on sheet one. I have put the second columns worth of data in a dropdown list in cell l1 and I need the corresponding price listed in the first column to self populate in cell m1 when that product is selected from the drop down in cell l1.

JACUSTOMER-k4l0ocs8- :

the list of data is on a seperate sheet from the cell i am trying to populate

JACUSTOMER-k4l0ocs8- :
jsterq :

Using an If/else statement as described on this web page: http://wiki.answers.com/Q/How_to_write_an_If_then_else_statements_in_excel
We can create an equation to populate the m1 cell.

For example, you would use a similar equation to this one for the m1 cell:
=IF(l1="Data 1","\$1.00",IF(l1="Data 2","\$3.00",IF(l1="Data 3","\$5.00",IF(l1="Data 4","\$7.00","\$9.00"))))

JACUSTOMER-k4l0ocs8- :

I have over 100 products is there an easerier way

jsterq :

Here is another page to help describe using an if/else statement: http://www.techonthenet.com/excel/formulas/if_nested.php

Just keep in mind you can only nest up to 7 IF functions in one cell.

JACUSTOMER-k4l0ocs8- :

so is there an easier way

jsterq :

I think I found another option

JACUSTOMER-k4l0ocs8- :

ok

jsterq :

Using the "Choose" function from Excel (http://www.excelmatic.dhakkanz.com/function-choose/)

You would enter the values based off an "index" for your multiple select list. You will have to limit your dropdown list to be a set of numbers (1-100) based on the possible data from the other sheet.

So in cell M1 you would enter something similar to this:

=Choose (L1,”Price One”, “Price Two”, “Price Three”, “Price Four”, “Price Five”, “Price Six”, “Price Seven”, “Price Eight”, “Price Nine”, “Price Ten”)

jsterq :

or something like this: =Choose (L1,”\$20.00”, “\$15.00”, “\$254.15”, “\$45.00”, “\$3.00”, “\$57.00”, “\$100.20”, “\$2.00”, “\$6.00”, “\$8.00”)

JACUSTOMER-k4l0ocs8- :

I can't scroll up on the screen to see anything above the web address us sent

Expert:  jsterq replied 4 years ago.
Expert:  John D replied 4 years ago.

Hi,

If your problem has not been resolved yet I will try to custom fix it for you on your file if you can send me the file. Let me know if you need help attaching the file