+ Reply to Thread
Results 1 to 4 of 4

Populate a group of cells below Drop Down Menu based on Drop Down Selection

  1. #1
    Registered User
    Join Date
    01-31-2013
    Location
    Edmonton,Alberta
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Populate a group of cells below Drop Down Menu based on Drop Down Selection

    Hi everyone!

    I have very basic knowledge of excel, and I"m trying to create a sheet that will populate a number of questions based on what the user selects from the drop down menu.

    For the attachment, basically what I want to happen is "if" user selects Drop1 from the menu on sheet1 , the cells below in yellow will auto populate with the information listed on sheet 2 below the Drop1 heading ( cells B2-B31 ) "OR" if they select Drop2 it should populate the yellow cells on Sheet1 with the values below Drop2 on Sheet 2 ( cells C2 - C13 ) and so on.

    What would be the best way to achieve this? Although I would definitely accept a "use this" with the values input, I would really like to try to understand whatever the solution is , so forgive me if I ask for clarification!

    Thank you in advance for any help!

    EDIT; Sorry, this was meant to be posted in the Beginner Sub Forum, Please move this as you see fit!"
    Attached Files Attached Files
    Last edited by Klaws; 01-31-2013 at 01:34 PM. Reason: Wrong Sub Forum

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Populate a group of cells below Drop Down Menu based on Drop Down Selection

    Welcome to the Forum!

    Put this formula in B12 and copy down through the yellow cells:

    =HLOOKUP(B$10,Sheet2!$A$1:$K$31,ROW()-ROW(Sheet1!B$12)+2,FALSE)&""

    Here is how it breaks down.

    HLOOKUP will look across a row for a match of a particular value, and then return the value a specified number of rows beneath the value it found. The function is of this format:

    HLOOKUP(value, range, relative row [, nearest match])

    value is the value you are looking for in the first row
    range is the range of cell in which you are searching (1 means the first row, which contains value, so you usually are going to use a number >1)
    relative row is the row within the range containing the value you want the function to return
    nearest match is TRUE if the data is sorted and you want the highest match at or below what you are searching for, FALSE if you require an exact match

    The value is the value in the dropdown box. The range is the entire range of cells on Sheet2 that you are searching. To count down the correct number for relative row, you want to take the row number of the cell containing the formula minus the first cell containing a formula, then add 2. A little study will show you why the arithmetic works that way. For nearest match you require an exact match.

    After all that, we append a null string at the end

    &""

    If the cell found is blank, it will be interpreted as a 0 and you'll see a 0 in the cell with the formula. If you append a null string to it, it forces Excel to treat it as a string and you will get a blank cell as a result.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    01-31-2013
    Location
    Edmonton,Alberta
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Populate a group of cells below Drop Down Menu based on Drop Down Selection

    Quote Originally Posted by 6StringJazzer View Post
    Welcome to the Forum!

    Put this formula in B12 and copy down through the yellow cells:

    =HLOOKUP(B$10,Sheet2!$A$1:$K$31,ROW()-ROW(Sheet1!B$12)+2,FALSE)&""

    Here is how it breaks down.

    HLOOKUP will look across a row for a match of a particular value, and then return the value a specified number of rows beneath the value it found. The function is of this format:

    HLOOKUP(value, range, relative row [, nearest match])

    value is the value you are looking for in the first row
    range is the range of cell in which you are searching (1 means the first row, which contains value, so you usually are going to use a number >1)
    relative row is the row within the range containing the value you want the function to return
    nearest match is TRUE if the data is sorted and you want the highest match at or below what you are searching for, FALSE if you require an exact match

    The value is the value in the dropdown box. The range is the entire range of cells on Sheet2 that you are searching. To count down the correct number for relative row, you want to take the row number of the cell containing the formula minus the first cell containing a formula, then add 2. A little study will show you why the arithmetic works that way. For nearest match you require an exact match.

    After all that, we append a null string at the end

    &""

    If the cell found is blank, it will be interpreted as a 0 and you'll see a 0 in the cell with the formula. If you append a null string to it, it forces Excel to treat it as a string and you will get a blank cell as a result.

    Wow,

    That was a perfect explanation. Makes perfect sense to me and works flawlessly. The arithmetic doesn't make a lot of sense to me yet but I have a lot of studying to do as it is. Thank you so much for the help! I cant wait until I progress enough with excel and these types of formulas where it just "clicks" in my head

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Populate a group of cells below Drop Down Menu based on Drop Down Selection

    Well, the first cell with the formula is B12. So you want to count how many rows down from B12 you are to know how many rows down in the lookup data to go. But then you have a problem known in computer science as the "off by 1 error". If B12 corresponds to the first row of data, and you subtract 12-12, you get 0, but you want 1. So you have to add 1 back to get 1. The other problem is that the first row in the data is the headers, so the first row of data is row 2. So you have to add another 1 for that.

    Therefore the row number within the lookup data is the row number of the formula minus the row number of B12 plus 2.

    By the way, around here they tend to frown on quoting an entire post when you reply. It adds a lot of text that is already found immediately above your reply. Quoting is OK when there is something specific you want to respond to (then just quote that part).

+ 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