Putting any part of array formula within quotes fails.

My static expression for array formula is: {=TRANSPOSE([Condos.xlsx]Units!$A$1:$C$45)}

Concatenation fails: {=TRANSPOSE("[" & Link2FileName & "]Units!$A$1:$C$45")}
Each cell is populated with [Condos.xlsx]Units!$A$1:$C$45 - does not pull data.

So it does get the filename string but does not seem to be any way to combine that variable reference with cell reference, whether it be a Table or sheet!range.

Okay, the INDIRECT is now working. {=TRANSPOSE(INDIRECT("[" & Link2FileName & "]Units!$A$1:$C$45"))}
Thought I had tried that before, but must not have got it right.

However, if you want to pull this data without having to open the downloaded file, consider options.

Power Query Add-in looks like one https://excelquick.com/excel-power-q...mic-file-path/
I expect this means every user would have to download and install PowerQuery add-in.

Otherwise, VBA and CopyFromRecordset method is another.