Formula to copy and paste multiple cells from Sheet 2 to Sheet 1 using dropdown

[KGards07]

Hi, I have a two sheets on my excel spreadsheet - Sheet1 and Sheet2. I have set up a drop down function in cell A2 in Sheet1. In sheet2 I have the titles in the drop down going along row 1 from A1-DI. Beneath each of the titles in Sheet 2 are several cells of information relevant to that specific title.

My goal is to create a formula that will drag through the cells of information from Sheet2 into Sheet1 into cell A3 onwards depending on what title is selected in the drop down in A2 e.g. in the drop down in Sheet 1 cell A2 if I selected "John Smith" then the information in Sheet 2 below "John Smith" would appear in Sheet 1 cell A3 and descend until all of the relevant information would appear in Sheet 1.

Thank you in advance for your help, it is greatly appreciated.

 

[My Aswer Is This]

I could provide a Vba script to do this. But your saying you want this done with a formula.

I'm not good with formulas. So we will wait and see if someone here has a way of writing a formula to do this.
If not I can write you a Vba script.

 

[wideboydixon]

The first thing you need is to find the column on Sheet2 of the selected item in Sheet1 which is this:

=MATCH($A$2,Sheet2!$A$1:$DI$1,0)

Then you can use this so fetch from the right place in Sheet2. Your formula in A3 would then be:

=INDEX(Sheet2!$A2:$DI2,MATCH($A$2,Sheet2!$A$1:$DI$1,0))

But you probably want to cater for the case where there's no match:

=IFERROR(INDEX(Sheet2!$A2:$DI2,MATCH($A$2,Sheet2!$A$1:$DI$1,0)),"")

Tricky without seeing the actual data. You can copy the formula in A3 down as far as possible. Untested of course ...

WBD

 

[My Aswer Is This]

If you want a Vba solution then try this:

Sub Search_Me()
'Modified 3-28-18 6:00 AM EDT
Dim ans As Long
Dim Lastrow As Long
Dim r As Long
Dim SearchString As String
Dim SearchRange As Range
SearchString = Sheets(1).Range("A2").Value
Set SearchRange = Sheets(2).Range("A1:D1").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox "Not Found": Exit Sub
ans = SearchRange.Column
Lastrow = Sheets(2).Cells(Rows.Count, ans).End(xlUp).Row
Sheets(2).Cells(2, ans).Resize(Lastrow).Copy Sheets(1).Range("A3")
End Sub