Using named cell range in a formula

[garyj]

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

[June7]

I tried. Cannot find any way to dynamically reference file name in array formula. I tried with INDIRECT. Concatenation doesn't work (as you already knew).

Interesting, where you show apostrophes I have to use [ ]. Maybe because I did not reference a defined table, just worksheet and cell range.

[Aflatoon]

INDIRECT won't work with a table ref. I'd suggest Power Query instead of formulas.

[p45cal]

try:

=TRANSPOSE(INDIRECT("'" & Link2FileName & "'!Table1[[#All],[Name of Group]:[Contact2 Phone]]"))

[Aflatoon]

Apologies - my mistake. It does work with table refs. (though I'd still use PQ )

[June7]

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.

[garyj]

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

[Aflatoon]

If .Formula2 works, then your users all have PQ available as a built-in functionality.

If you are going to code it, you could simply update the external links in one go.

[p45cal]

it would be tough to train user(s) to download and install PQ each time a form comes in. Sigh..

From the subject title of your last thread here ( http://www.vbaexpress.com/forum/show...ent&highlight= ) you, at least, have a version of Excel with Power Query built-in; it's in the Data tab of the ribbon in the section Get & Transform Data. Your users will also have it built-in if they have a version of Excel from Excel 2016.
As far as training those users is concerned, you might only need to tell them to right-click a table and choose Refresh (although this can be automatic too, either with a macro or on a timer).
You'd still need a macro to identify the latest file but that would not need to be opened.

[June7]

That explains why my expression is different and I had to download and add PQ. I am using Excel 2010.

[garyj]

Screenshot Dec11.jpgYou are Excel sleuths, ye three. and you all make valid points.

Yes, using 365 here, and requiring my users to do so also. Right now they have Microsoft on the Web, but that program will not allow VBA, so it causes problems with what I am trying to accomplish for the company with this program.

My reason for having the data viewed by the user is that someone could have added a question in MSForms or changed the wording.. All my fancy footwork of using code to put those questions into either Clients_Tbl or Booking_Tbl would then unravel. Downloading and viewing allows the user to drag drop Field names (named cells) to line them up with the questions in MSForms. It's kind of old school, but not sure if there is another way. The field names are organized and understood at the company level. Questions on the form are organized and understood at the client level. So wording and order should be changeable. The only reason they would require more programming would be if they wanted to add a question.

So this whole Filter thing, which could be replaced by PQ, is rather temporary. What would be the benefit of the PQ since my Filter function is now coded in and works?

Hmm... Just thinking here. I could get the dispatch to write all the questions from the MSForm into a sheet on column A, exactly as worded in the forms, then to put the matching field names, exactly as used in Booking and Clients in Columns B and C, respectively. Order shouldn't matter. Then I could take the length of the Column A, and use that to iterate through the items in Column C to create a new row in Clients, and then do the same for Booking, by using for each item in Column B. Maybe this would be a better code approach anyway, and it would save the user from having to match fields. The download would not need to be opened. And the sheet only updated if changes are made to field names or form questions.

Here is a view of my sheet.