Lookup dropdown list value in a another sheet and copy row accordingly

Solved/Closed
Torrey - Updated on May 18, 2021 at 11:53 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 1, 2021 at 11:43 AM
Hello,

I am trying to find out if I can create a drop-down list in Sheet1 that when I make my selection, it will copy part of a row from Sheet2 onto Sheet1 next to my selection?
Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 25, 2021 at 12:09 PM
Hi Torrey,

Could you provide some more details?
Do you know how to create a dropdown list?
When you select a value from the dropdown list, how do we know which part of sheet2 needs to be copied?

Best regards,
Trowa

I do know how to create a dropdown list.

My dropdown is in column A from sheet2. I want the data from that row, that is stored from Column B thru Column AS to be copied to sheet1 onto the same row as the dropdown cell, going from the dropdown cell to the right.

Basically, I am trying to create a bid spreadsheet that has multiple calculators that I have already created and stored one calculator per row in sheet2. Column A of sheet2 is the name of the calculator.

When I create a bid on sheet1, I want to be able to select a calculator (Row) from the dropdown list and have only that row from Column B to AS copied into my bid sheet.


Thank you for helping me with this.


Torrey
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Mar 1, 2021 at 11:51 AM
Hi Torrey,

The code below will look up the value from the dropdown list in column A of Sheet1 in column A of Sheet2. If a match is found, it will copy the row of column B to AS of Sheet2 to column B of Sheet1.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Columns("A"), Target) Is Nothing Then Exit Sub
Dim mFind As Range
With Sheets("Sheet2")
    Set mFind = .Columns("A").Find(Target.Value)
    If mFind Is Nothing Then Exit Sub
    .Range(.Cells(mFind.Row, "B"), .Cells(mFind.Row, "AS")).Copy Target.Offset(0, 1)
End With
End Sub


To implement the code, right-click Sheet1's tab and select 'View code', then paste the code in the big white field. The code will run automatically when you select a value from one of your dropdown lists in column A of Sheet1.

The name of Sheet1 (or your bid sheet) doesn't matter, but make sure your second sheet is named Sheet2. Or replace Sheet2 in the code with your sheet name.

Best regards,
Trowa