How to link separate Excel files to auto-populate cells

Originally Posted by chrlyd

My question regarding the formula is that I don't know how to apply this to my actual data. What formula do I enter and where to achieve these results with my data and as a I create new data sheets.

Do you mean actual header names and/or position of header in csv are different from your files?

If so, this code will find the position of each header in csv from the header in row(1) of the worksheet.
Try replace "test" sub with the below, keeping other procedure/function remain intact.

Sub test()
    Dim fn As String, a, b, s, x, y, temp
    Dim i As Long, ii As Long, dic(1) As Object
    For i = 0 To 1
        Set dic(i) = CreateObject("Scripting.Dictionary")
        dic(i).CompareMode = 1
    Next
    a = Sheets("sheet1").[a1].CurrentRegion.Value
    b = Application.Index(a, 1, 0)
    For i = 2 To UBound(a, 1)
        If a(i, 1) <> "" Then dic(0)(a(i, 1)) = Array(a(i, 1), a(i, 2), a(i, 3), a(i, 4))
        If a(i, 2) <> "" Then dic(1)(a(i, 2)) = Array(a(i, 1), a(i, 2), a(i, 3), a(i, 4))
    Next
    fn = ThisWorkbook.Path & "\example_datasheet.csv"
    x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll, vbNewLine)
    y = Split(CleanCSV(x(0), Chr(2), Chr(3)), ",")
    For ii = 1 To UBound(b)
        b(ii) = Application.Match(Replace(b(ii), ",", Chr(2)), y, 0)
        If IsError(b(ii)) Then MsgBox "Field " & a(1, ii) & " is missing", vbCritical: Exit Sub
        b(ii) = b(ii) - 1
    Next
    For i = 1 To UBound(x)
        If x(i) <> "" Then
            y = Split(CleanCSV(x(i), Chr(2), Chr(3)), ",")
            For ii = 0 To 1
                s = Replace(y(b(ii + 1)), Chr(2), ",")
                If dic(ii).exists(s) Then temp = dic(ii)(s)
            Next
            If IsArray(temp) Then
                For ii = 1 To UBound(b)
                    If temp(ii - 1) Like "*,*" Then temp(ii - 1) = Chr(34) & temp(ii - 1) & Chr(34)
                    y(b(ii)) = temp(ii - 1)
                Next
            End If
            x(i) = Replace(Replace(Join(y, ","), Chr(2), ","), Chr(3), """")
        End If
    Next
    fn = Replace(fn, ".csv", "_Updated.csv")
    Open fn For Output As #1
        Print #1, Join(x, vbNewLine);
    Close #1
    ImportCSV fn
End Sub