+ Reply to Thread
Results 1 to 9 of 9

Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Marble Falls, TX
    MS-Off Ver
    Excel 2003
    Posts
    4

    Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    Hello all,
    I'm trying to accomplish exactly that. I am fairly familiar with these formula's and data validation. But I can not seem to put these two together and make it work.

    I have several tanks listed as the type they are and in column 1 "inches", column 2 "volume for each tank. I want to have a drop down list that selects which tank, and then I can type in the inches and it presents the volume.

    I am not familiar with arrays. If this is something that needs to be in a array I would need to know so I can read up and learn how to create them.

    Any help would be appreciated. And if need be I would be able to attach the spread sheet to this post.

    Thanks,
    Ryan

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    Can You please post a sample?

  3. #3
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    Hello,

    What you are interested in might be in this thread
    http://www.excelforum.com/excel-form...down-list.html
    If not, like mehmetcik suggested, please upload a sample file, it will be easier for others to help you.
    (copy pasta from Ford)
    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

    Regards,
    Lem

  4. #4
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    I'm not 100% sure on your tank data layout from the description but I think you will need:

    COL A - TYPE
    COL B - INCHES
    COL C - JOIN
    COL D - VOLUME

    Enter you data for A B and D. In Col C starting in C2 type =A2&B2 than drag as far as required. This creates a unique type&inches value.

    I will assume this is on 'Sheet2' and on 'Sheet1' you have a drop down in A1 listing tank types. In B1 you enter a volume. In C1 type =VLOOKUP(A1&B1,'Sheet2'!A:D,4,0). This should return your volume accordingly.

    (Note: I assume 2003 vlookup is the same, may need replace 0 with FALSE in the formula if not working. Don't use it so am guessing)
    Last edited by Harribone; 04-22-2013 at 03:43 PM. Reason: "C2 type =A1&B1" should be "C2 type =A2&B2"
    Say thanks, click *

  5. #5
    Registered User
    Join Date
    04-04-2013
    Location
    Marble Falls, TX
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    Okay lets see if this can clear up what I am asking. I have this list of tanks with inches and volumes.Capture1.JPG
    And I am trying to put a drop down list on the name of tanks, and tie my vlookup formula to it.
    Capture.JPG

    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    Marble Falls, TX
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    Harribone,
    Will try, thanks.

  7. #7
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    I don't know where your drop down and volume is so to help me explain lets assume the drop down cell is DROP and the inches cell is INCH.

    On the XXXXX sheet where you have each tank listed with corresponding data I suggest you perfrom a vlookup for each tank.
    So in A3 on this sheet type =VLOOKUP(INCH,A5:B67,2,0) this will lookup the inches you type in and return the volume. repeat this for each tank, changing the formula accordingly.
    If your drop down list of tank names isn't exactly the same as shown in row 4 of XXXXX then change these to match.

    You now have the volumes for each tank which matches the inches. You just need to get the right volume from these which relates to the drop down list of tank names.
    In the cell where you want the correct volume type =INDEX(XXXXX!3:3,MATCH(DROP,XXXXX!4:4,0)).
    This will find the tank name in row 4 and return the volume for that tank in row 3 (which has been matched up to the inches value already).
    Last edited by Harribone; 04-22-2013 at 03:46 PM.

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    Marble Falls, TX
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    This would work great, and I have tried it. However I was hoping to have several independent drop down list for several tanks on the next sheet. I could make it work this way for each using a combination of index and matching. I was just curious if there was a easier way. I was trying to make your first example work because that would solve the issue of multiple dropdown list. Guess there is not going to be a easy way about it.

  9. #9
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Dropdown list, Vlookup (table), Enter in number, and find cell referenced?

    Not sure what the issue of multiple drop down boxes is exactly in your work, both ways should work for multiple look ups.

    The attached file is an example of howto use the first method I suggested. I've put on one sheet for ease and used mock data for quickness.
    Try it with a few lines of real data if this helps you understand how it works better.

    Note I have also used ISERROR in L4 so tht this remains blank when there is nothing to look for/nothing found. I think this is how to avoid errors using 2003 but i'm not sure. If there are issues in 2003 let me know.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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