Hello Excel World...

Sometimes the simplest of things can look the hardest, and this is probably one of those cases. I just can't find the answer anywhere.

I am trying to use the worksheet level Transpose formula to read data in a columnar view from a downloaded Microsoft Forms response file. The form downloads as and Excel, Table1.

This works...
=TRANSPOSE('_Coach Booking Form(1-2).xlsx'!Table1[[#All],[Name of Group]:[Contact2 Phone]])
The problem with it is that if the user forgets to erase the original file, it will come into downloads the next time as '_Coach Booking Form(1-2)(1).xlsx', or even if they do erase it, the second number in "(1-2)" is a reference for the number of rows that are on the downloaded file. I don't yet know what the 1 does, or if that presents even more problems.

So I found some code that goes to the downloaded folder and writes the most recent "Like" named file into a cell. I named that cell as "Link2FileName". But no matter where I put the quotes and the ampersands, or if I remove them, it doesn't work.

These do not work...
=TRANSPOSE("'" & Link2FileName & "'"!Table1[[#All],[Name of Group]:[Contact2 Phone]])
=TRANSPOSE(Link2FileName!Table1[[#All],[Name of Group]:[Contact2 Phone]])
I want the user to be able to download the file from MS Forms, open it, and go back to the main sheet to find that all the columns are already viewable through a wisely placed Transpose array. Their next purpose would then be to pair the named fields to match those from the downloaded form, and then to select "Import Data" to the spreadsheets. I think as part of that Sub I will finish by programmatically closing and deleting the download. That will solve any possible copies, but I still have issue with the file name.

Thanks again
Gary