+ Reply to Thread
Results 1 to 9 of 9

How Can I return an adjacent value based on the choice of my drop down list?

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    7

    How Can I return an adjacent value based on the choice of my drop down list?

    Hello,

    I have a question, I just learned to use drop down lists using validation.

    Here is the basic mechanics of my question.

    On one tab I have two columns. One is a parts column and adjacent is a corresponding price column.

    On another tab i have a drop down list for the part, When i choose my part i want the price for that part to be entered as well.

    Is there a simple way to do this?

    Thanks in advance I am new to this forum.
    Last edited by William_IV; 10-04-2012 at 03:26 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    You can have the price enter in the next cell. If your dropdown is in A1, in B1
    =IF(ISBLANK(A1),"", VLOOKUP(A1,sheet2!$A$1:$B$200,2,FALSE))
    Does that work for you?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    ChemistB,

    Yep that works, I thought it would have to be some kind of IF statement. I haven't learned VLOOKUP yet, will review the command and play with it. Thanks again!

  4. #4
    Registered User
    Join Date
    10-02-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    ChemistB,

    Your solution led to an additional bug, perhaps it's just my lack of experience working with formulas.

    Here is the problem:

    How can i fill down your formula so i could select one of six parts for a hundred rows without having to manually change the formula for each row?
    I tried auto filling. but the values in the formula change then i have to manually change them back at every row.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,306

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    if you are using A1 as your drop-down, , you need to absolute that to copy it down $A$1. but that begs the question...why would you want to? if you use the above formula =IF(ISBLANK(A1),"", VLOOKUP(A1,sheet2!$A$1:$B$200,2,FALSE)), and just absoluted A1, you would get exactly the same answer in every cell you copied it to
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    Yep, I'm not sure what you are looking at now.
    Are you saying that you have multiple dropdown cells (i.e. A1, A2, A3...)?
    Or are you expecting multiple results from a single dropdown (i.e. if A1 = red, there are multiple matches, "apples", "cherries" "strawberries") and you want to return all of them in different cells (much more complex and we'd need to start over)?
    or something else?

  7. #7
    Registered User
    Join Date
    10-02-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    ChemistB, Your right i should explain better.

    I have two sheets

    On sheet2 there is two columns, one column (A1thru A100) is parts for construction, for example pipe, elbows, tees,unions. Beside the same column is another column of prices for the part,(B1thru B100). sheet 2 is my template and data base of parts and pricing.

    Sheet one is my form for estimating a specific project. Cell A1 is a drop down list of the parts in sheet 2 Acolumn. When I select tees I want the price for the tee entered as well. I might also need to select tees several times depending if the project is broken down.

    For example:

    Sheet one may look like this:

    Tees 2.50
    Elbows 1.00
    Tees 2.50
    Tees 2.50
    Tees 2.50
    Pipe 3.00
    Unions 4.50

    Basically i'm looking to select the part and have the price for it returned as well. then when i finish my sheet1 estimate i save that sheet for the project i'm working on, and then start a new estimate for project 2 etc. etc.

    Hopefully this is more clear on what i'm trying to accomplish.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    Take a look at the attachment. Does this make sense? Is this what you are trying to achieve? Questions?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-02-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How Can I return an adjacent value based on the choice of my drop down list?

    ChemistB,
    Your a Genius! works great!

    The first string you gave me at the bigginning of my thread I was unable to grab colum b and pull down the formula without the formula changing on me, therefore I would manually have to change each formula.

    Using your attachment I could pull down the formula and extend sheet one to A250 without difficulties. I noticed only the lookup values changed as needed and the rest of the string remained unchanged. Thanks again your spreadsheet was exactly what i was trying to accomplish, I learned a lot from this thank-you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1