Excel/Drop down listbox and copy next cell
I’m trying to setup a drop down listbox with a twist, let me explain in more detail.
This is what I have so far: a workbook with 2 worksheets, let’s say the Tabs are called sheet1 and sheet2 (original I know). In sheet1 there will be names in column “A” starting at “A5” down, this will be added to over time. In sheet2 there will be a drop down listbox(s) that will be populated with any of the names in sheet1. This works, it’s not dynamic but it works, this is what I have in the data validation source box: =Sheet1!$A$5:$A$49 .
This works fine, but the twist is this, when the name is selected from the drop down listbox (in sheet2) I would like to have what is in “column B” copied to the cell below. Let me explain in more detail with some examples. In Sheet1 in “A5” it has the name Bob, in “B5” it will have a country, England, then “A6” Bill and “B6” France, the “A7” Tim and “B7” Japan and so on.
Then on sheet2 when one of the names are selected from the drop down listbox, let’s say Tim, I would like to copy what’s in the next column (“B” in sheet1), in this case Japan to the row directly below the drop down listbox. So if the drop down listbox is in cell “A10” and Tim is selected then Japan will be copied to “A11”.
If the drop down listbox is in cell “A17” then Bob is selected, then England will be copied to “A18”.
If the drop down listbox is in cell “A25” then Tim is selected, then Japan will be copied to “A25” and so on.
I have tried looking at example on the web, but I just don’t know how. I think the “offset” function might have to be used.
I hope this make sense and I would be happy to clarify anything if I have haven’t explained myself properly.
I am using excel 2016.
You cite one of the dropdowns as being in cell A10 on sheet 2.
in cell A11 you should put in the formula
Then when a value is selected in A10 on sheet2, the vlookup will go to sheet1 and return the matching value from column B.
The second argument in a vlookup is the lookup range. For vlookup, the value being looked up must be in the left hand column of the lookup range - in your case, column A. The third argument tells vlookup what column in the lookup range should be used to return a value from the matching row. The 2 in my example says column B which is what you want.
The last argument of Vlookup says to find an exact match. In your case an exact match is appropriate. This also doesn't require the lookup range to be sorted by the leftmost column.
If there is anything unclear, then post a followup and I can try to clarify. People seem to have a lot of problems with Vlookup, but it is a very useful function and is perfect for what you describe.
Your challenge will be keeping the lookup range reference in the formula updated to match the range your using in your dropdown. If you are going to add values, it would be best to insert rows within the existing data because then the range references will be expanded automatically to match.