How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
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
A new question is answered every 9 seconds

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.
Does this help you view the screen properly?
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