Drop down list with corresponding value within the same cell.

[GravityDead]

Hello guys!
Need some quick help here. I want to make a drop down list which shows data from "Column A" when selecting but enter the corresponding value from "Column B" when selected. I hope I made myself clear

For example, this is "sheet2"
Column A - Column B
1947 - 500
1948 - 1000
1949 - 2000
1950 - 1500

I want to make a drop down in another sheet, say "sheet1", in cell K2 and all the cells down below.
When I click on K2, the drop down list should show the options, "1947", "1948", "1949", "1950" BUT when I select any of these values from Columns A in K2, the K2 cell should be filled with corresponding value from Column B.

Is it possible to do so or I HAVE to do it indirectly?

Regards.

 

[mumps]

Click this link to download a sample file: https://app.box.com/s/yxw6ro4j8uppoe58mjnwed5fx2onp3uq
In sheet1 make a selection in column K.

 

[GravityDead]

Click this link to download a sample file: https://app.box.com/s/yxw6ro4j8uppoe58mjnwed5fx2onp3uq
In sheet1 make a selection in column K.

Could you explain how did you do it?
I see that you have given a name to the year list and in the data validation, you have chosen "list" and given the name in the list field!

But how do I get it to return the corresponding value? I made my query very simple, in reality it is a bit more complex that is why I'm asking you this

 

[mumps]

I used a macro in the worksheet code module. To view the macro, right click on the tab name and click 'View Code'. The macro runs automatically when you make a selection in column K. It is always best to work with your actual file instead of a simplified version of it. A macro that works with the simplified version may not work with your actual version. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[GravityDead]

ahh I was afraid that it won't be possible without a Macro
I guess I'll have to use additional columns to achieve the objective without using a macro as I'm creating a template for another user who is a bigger noob than me. Thank you for your time though :D

One more thing, just another quick question, if you don't mind!
For example, I have accounting data in column B:E and date in column A, I want data validation in columns B:E that unless date column (column A) is filled, the user cannot enter accounting data in corresponding columns.

 

[GravityDead]

never mind for the last query. I solved it myself using

A2<>""

.


thank you mumps :D

 

[mumps]

My pleasure.

 

[GravityDead]

I'm stuck right now, cannot think of making it more easier.
mumps, could you make that macro for my specific file, I'm attaching a sample data file here with a similar format to my original file.

K2:K & L2:l is where I want the drop-down boxes to appear. values to be selected to be shown while selecting are in column A and values to be entered are in the column D of the second sheet.
I guess I can learn a bit of VBA too this way :D

Sample file Link

 

[mumps]

I'm sorry but my antivirus software blocks the link you posted. Could you use www.dropbox.com or www.box.com?

 

[GravityDead]

Is OneDrive okay?
Here's a OneDrive link, let me know if you can only download from Box or Dropbox, I'll signup there then.