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

**Klaws** · 01-31-2013, 01:31 PM

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!"

**6StringJazzer** · 01-31-2013, 02:02 PM

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.

**Klaws** · 01-31-2013, 03:21 PM

Originally Posted by 6StringJazzer

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

**6StringJazzer** · 01-31-2013, 06:22 PM

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).