Using Column Header Name to Populate data

[britsgal]

I have a spreadsheet where the first tab is a template with formulas pulling data from a 2nd sheet within the workbook. I receive a "dump" from another system, paste that into sheet 2 and the columns are auto-populated on sheet 1 using =A1="sheet 2" A1. The columns would be identical (the same header name) in order for this to work properly. Now someone has moved the columns around but I still need the columns in the original order on sheet 1. Is there a way to populate the columns on sheet 1 based on the column name and find the identical column name on sheet 2 to populate the column with the identical name on sheet one. As you can see from the below screen shot, I want the columns to reflect sheet 1 columns using the new position of sheet 2 columns (highlighted orange) but still pull the data into sheet 1 in the columns as shown. I hope this makes sense. NOTE: the columns in sheet 2 could change again as I do not control the system where the data is pulled from for sheet 2. Any help is appreciated.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[britsgal]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

My apologies.....using Office 365 on Windows. Updated profile with this information.

 

[Fluff]

Thanks for that.
How about

=SORTBY(Sheet2!A2:J20,MATCH(Sheet2!A1:J1,A1:J1,0))

 

[mumps]

Macro approach:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, LastRow As Long, lCol As Long, header As Range, x As Long, rng As Range
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each rng In desWS.Range("A1").Resize(, lCol)
        Set header = srcWS.Rows(1).Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not header Is Nothing Then
            srcWS.Range(srcWS.Cells(2, header.Column), srcWS.Cells(LastRow, header.Column)).Copy desWS.Cells(2, rng.Column)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[britsgal]

Thanks everyone. I actually found another option from someone in my office. I appreciate everyone's help!

 

[Joe4]

Thanks everyone. I actually found another option from someone in my office. I appreciate everyone's help!

Would you care to post that solution here?

 

[britsgal]

Yes, we did it with an hlookup and a row designation