Selected Answer
Sue,
You can't copy cell formatting with just just an IF or VLOOKUP formula and your data doesn't lend itself to a solution using Conditional Formatting (without further information on your source) nor doing a custom horizontal (left to right) sort, since you don't seem to report all columns from the source "raw" data. However...
It is possible to do what you want using VBA (Excel's built-in programming language). In the attached file (a version of your training file but which will need macros enabled) I've copied your report sheet to the end (see tab named "Question VOC") so it can be used to compare results).
I've then add added a green button stating "Import columns" in cell A1 of the first worksheet "VOC". 'Behind' that is the macro below (in VBA's Module1). I'm assuming you don't know VBA but if you have your imported data in a second sheet (like your Sheet1) and click that button (with macros-enabled), it will check some columns match (or tell you) and proceed to copy matching columns from Sheet1.
To get an idea of what's happening, follow the comments -red text lines below (starting with ')- especially the major steps starting ' # and in bold.
Option Base 1
Sub SortKeepingFormats()
Dim RepLstCl As Long, SrcLstRw As Long, SrcLstCl As Long
Dim ColXRef() As Variant, CpyRng As Range, Mtch As Boolean
Dim m As Long, n As Long
' speed up for large data sets
Application.ScreenUpdating = False
' #1. work out some sheet dimensions
RepLstCl = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
SrcLstRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
SrcLstCl = Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column
'# 2. size cross reference array
ReDim ColXRef(1 To RepLstCl, 1 To 2)
' #3. cross reference headers in report (to source)
For n = 1 To RepLstCl
'populate the report columns
ColXRef(n, 1) = Sheets(1).Cells(1, n)
' loop through source to find matching headers
For m = 1 To SrcLstCl
'check report column c.f. array value
If Sheets(2).Cells(1, m).Value = ColXRef(n, 1) Then
ColXRef(n, 2) = m
If n > 1 Then Mtch = True ' show a column match was made
End If
Next m
Next n
' check some columns matched
If Mtch = False Then
MsgBox "No matching data columns; original data left intact"
Exit Sub
Else
' clear data from row 3 and continue
Sheets(1).Rows("3:1000").Delete
End If
' #4. Copy data from source to report using cross refs
' go across headers
For n = 1 To RepLstCl
' if first column or there's a cross ref to source
If n = 1 Or Not IsEmpty(ColXRef(n, 2)) Then
' copy source rows 2 to end
With Sheets(2)
Set CpyRng = .Range(.Cells(2, ColXRef(n, 2)), .Cells(SrcLstRw, ColXRef(n, 2)))
CpyRng.Copy Destination:=Sheets(1).Cells(3, n)
End With
Else
'otherwise collect headings not imported
NoX = NoX & ColXRef(n, 1) & vbCr
End If
' write borders and fill background like header
With Sheets(1).Range(Cells(3, n), Cells(SrcLstRw + 1, n))
.Borders.LineStyle = xlContinuous
.Interior.Color = Sheets(1).Cells(1, n).Interior.Color
End With
Next n
Application.ScreenUpdating = True ' restore
' tell user what did/ didn't happen
If NoX <> "" Then MsgBox "Imported from source " & Now & " but didn't find matches for report headers:" & vbCr & vbCr & NoX
End Sub
Currently all the dots show green but if you copy a red one on Sheet1 to an imported column (say F, Contruction Blue/White Card) then you'll see it gets imported correctly.
Note that if it doesn't care how many rows or columns you have in your 2 sheets (provided there are no more that 1000 rows in VOC) and will copy as many source rows and matched columns as there are- if there are 4 rows in your source, you'll get 4, if 230, you get 230 etc.
Also if you add a fill to a row 1 header in VOC (say blue in matching column C as I have), the data will copy but with that colour background added.
Hope this makes some sense and helps. If so, please remember to mark the Answer as Selected.