Consulting

Results 1 to 11 of 11

Thread: Using named cell range in a formula

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location

    Using named cell range in a formula

    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

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,736
    Location
    INDIRECT won't work with a table ref. I'd suggest Power Query instead of formulas.
    Be as you wish to seem

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,904
    try:
    =TRANSPOSE(INDIRECT("'" & Link2FileName & "'!Table1[[#All],[Name of Group]:[Contact2 Phone]]"))
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,736
    Location
    Apologies - my mistake. It does work with table refs. (though I'd still use PQ )
    Be as you wish to seem

  6. #6
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,736
    Location
    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.
    Be as you wish to seem

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,904
    Quote Originally Posted by garyj View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    334
    Location
    That explains why my expression is different and I had to download and add PQ. I am using Excel 2010.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •