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 your file: https://app.box.com/s/yxw6ro4j8uppoe58mjnwed5fx2onp3uq

 

[GravityDead]

ahh sorry bro, but the last file doesn't seem to work for me. it is not taking up values from Column D of the second sheet. See if you can correct it.

 

[mumps]

I just tried it and it's working properly for me. When I make a selection from columns K or L in the DailyInvoices sheet it pulls in the value from column D of the AdvanceNotes sheet. Also, I forgot to mention that the drop lists will update automatically when you add or remove years in column A of the AdvanceNotes sheet. Are you using the file I attached in Post #11 or are you using a different file?

 

[GravityDead]

Yes I'm using the file you attached in Post#11. Columns "K" and "L" does have drop down which shows data from column A of "AdvanceNotes" sheets but after selecting an option from the list, the result is also pulled from the column "A" only rather than column "D".

Also, I forgot to mention that the drop lists will update automatically when you add or remove years in column A of the AdvanceNotes sheet

Yeah, I kinda figured that out when I saw the validation formula

 

[mumps]

Try this macro in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K:K,L:L")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range
    If Target <> "" Then
        Set rng = Sheets("AdvanceNotes").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
        If Not rng Is Nothing Then
            Target = rng.Offset(0, 3)
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[GravityDead]

still not working. I'll try to compare the VBA with the first file you sent!

your first code which was working just fine

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim val As Range
Set val = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
If Not val Is Nothing Then
Target = val.Offset(0, 1)
End If
End Sub

I guess the column headers in my actual file is creating problems!

 

[GravityDead]

your first code works just fine in the file you sent and also in the new files that I make to test it but I don't know what's wrong with my current file that it just doesn't work. I guess it has to do with formatting of cells may be.
I just have to ignore it for now after these many tries

Anyhow, thank you so much, you really helped me out a lot Happy excel'ing

 

[mumps]

If you attach a link to your current file, I can have a look at it. It's always best to test a macro on the actual file.

 

[GravityDead]

I just checked it with a new file and the code seems to work as long as the columns are formatted as "General" but if I change it my local Indian currency format the code stops working.
If you can tell me what to change in the code, I'll do it myself else I can attach the file in few minutes.

 

[GravityDead]

If you attach a link to your current file, I can have a look at it. It's always best to test a macro on the actual file.

here is the current file sample, for your convenience I have created a copy of each sheet, one pair without currency format which is working as supposed to AND another pair of sheets with currency format which isn't working -_-

By the way, this file has made me searched a lot about VBA, I think I will learn basic VBA functions, at least.