populate a row based on cell input

[torreyanderson]

I have a dropdown list on sheet1. Column A of sheet2 contains the list items. Columns B thru M on Sheet2 contain data related to Column A.

Can I make a selection from my dropdown list on sheet1 that will then copy and paste the data from Columns B thru M from Sheet2 to the ROW in Sheet1 where I made the dropdown selection?


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 B to M) from sheet2 copied into my bid sheet.

 

[Fluff]

Hi & welcome to MrExcel.
Are the values in col A of sheet2 unique, or can they appear multiple times?

 

[torreyanderson]

They are unique.

Thanks for helping

 

[Fluff]

In that case how about in B2

=FILTER(Sheet2!B2:M500,Sheet2!A2:A500=A2,"")

 

[torreyanderson]

That is close.

Is there a way it can bring the calculations not just the value. These rows are basically custom calculators to arrive at the bid price for that item (row). So, my hope is to select it from the dropdown and then fill in the values and have the calculations supply the bid price.

 

[Fluff]

You cannot bring a formula across, if that is what you mean.

 

[torreyanderson]

is there any option using a macro tied to a combo box?

 

[Fluff]

What are the formulae you need to bring across?

 

[torreyanderson]

=IF(B2=0,0,(ROUNDUP((((B2*D2)/12)*G2)/2000,0)))

I don't know if I did that right, but that is in column L Row 2

Each row has a different formula but they are all similar

 

[Fluff]

Ok how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
      Set Fnd = Sheets("Sheet2").Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
      Target.Offset(, 1).Resize(, 12).FormulaR1C1 = Fnd.Offset(, 1).Resize(, 12).FormulaR1C1
   End If
End Sub

This needs to go in the sheet module for the sheet with the dropdowns