Oh my, sounds like I asked a toughie. I appreciate all of your answers, so I am responding to all at once.

I have heard negative things about Indirect causing problems, so am trying to avoid using it. Maybe it just got some bad rap?
Anyway, Power Query is a great idea and would have worked, if I could have done it programmatically. Yes, June, it would be tough to train user(s) to download and install PQ each time a form comes in. Sigh..

I've had a crazy week, but kept plugging away, and somehow forgot about this question until now. So I will share my solution in case it helps someone else. I solved it by programming the formula with VBA, rather than hard writing it. The code I used is here...

Sub Cmd_PrepareSheet_Click()
    GblmyStr = MostRecent("C:\Users\me\Downloads\", "_Coach Booking Form*.xlsx")
    On Error GoTo ErrFileNotExist
    Workbooks.Open "C:\Users\me\Downloads\" & GblmyStr
    Workbooks("MyDispatch.xlsm").Activate
    Range("Link2AllData").Formula2 = "=TRANSPOSE('" & GblmyStr & "'!Table1[[#All],[Name of Group]:[Contact2 Phone]])"
    Exit Sub
ErrFileNotExist:
    MsgBox "The file does not yet exist in Downloads.", vbInformation, "Please Note"
End Sub

Function MostRecent(ByVal Folder As String, ByVal Mask As String) As String
    Dim fso As Object 'FileSystemObject
    Dim f As Object 'File
    Dim LastDate As Date
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error GoTo ExitPoint
    For Each f In fso.GetFolder(Folder).Files
        If f.Name Like Mask Then
            If f.DateCreated > LastDate Then
                LastDate = f.DateCreated
                MostRecent = f.Name
                End If
            End If
        Next
ExitPoint:
End Function
That function [MostRecent] is the code I borrowed, which finds the latest file of a similar name. It works quite well with that wildcard. I got rid of Link2FileName, found that I didn't need it.
Link2AllData, of course, is where the first cell of the transposed spill from the newly opened sheet.
GblmyStr is as you guess, a global variable that I use to keep the file name. I used it here to open the file, and again at the end to open the file so that I could make it active and close it and delete it. I found it impossible with Dim wkb as workbook. But this worked. 

     ...
    Workbooks.Open "C:\Users\me\Downloads\" & GblmyStr    
    ActiveWorkbook.Close
    Kill Filepath
End Sub
It works quite well, so far.

Gary