'Excel - Using a cell reference as a TableName in a fully qualified structured reference

I am putting together a dashboard to quickly see counts of orders from vendors image of dashboard

The simplified version of the formula I am using is as follows:

=IFERROR(
        SUMPRODUCT(
              (TEXT(Table1[[openDate]:[openDate]],"YYYY")=$B$2)
               *
              (TEXT(Table1[[openDate]:[openDate]],"MMMM")=C$3)
              /
              COUNTIF(Table1[[orderNum]:[orderNum]],Table1[[orderNum]:[orderNum]]&"")
               )
        ,"")

Each table is a separate worksheet for each vendor. What I am trying to accomplish is have the table reference (Table1 in this example) be able to be a cell reference. This would allow the formula to be pre-populated in the dashboard and not require manually updating the table name each time a new table is created.

I have created a VBA module that pulls all table names and places them into a table on a reference worksheet image of reference table:

  Sub GetTableNameList()
    
        Dim x As ListObject
    
        Dim y As Worksheet
      
        Dim z As Long
        
            z = -1
           
            For Each y In Worksheets
            
            For Each x In y.ListObjects
            
                z = z + 1
        Sheets("Reference").Range("B3").Offset(z).Value = x.Name
        
        Next x
        
        Next
         
    End Sub

My thought is that as new tables are created the cell in the formula would reference the next line on the reference table. I am open to all other suggestions for how to complete this task. My only stipulation is there needs to be little to no interaction required by the end user for this to work properly.

NOTE: Dashboard begins blank, and there are no tables to reference. Tables/worksheets are added by the user as they select vendors they wish to track.

Update:

I am attempting to use my reference table by adding

TEXT(EvaluateString("Reference!$B6"&"[[openDate]:[openDate]])"),"YYYY")=$B$2)

Where "EvaluateString" is:

   Function EvaluateString(strTextString As String)
    
        Application.Volatile
    
        EvaluateString = Evaluate(strTextString)

    End Function

I feel like I am close, and it may just be a syntax error at this point. I need to find a way for the string to be recognized as a structured reference.

Solution 1:^[1]

You could use INDIRECT. E.g. With "Table1" entered into A1, TEXT(INDIRECT(A1&"[[openDate]:[openDate]]"),"YYYY") will be understood as TEXT(Table1[[openDate]:[openDate]],"YYYY")

Keep in mind, however, that too many INDIRECT formulas slows down Excel considerably. Since you're also using VBA, you could also consider something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1")) Is Nothing Then
    
        TableString = Target.Value
    
        Built_Formula = "=IFERROR(SUMPRODUCT((TEXT(" & TableString & "[[openDate]:[openDate]],""YYYY"")=$F$2)*(TEXT(" & TableString & "[[openDate]:[openDate]],""MMMM"")=F$3)/" _
    + "COUNTIF(" & TableString & "[[orderNum]:[orderNum]]," & TableString & "[[orderNum]:[orderNum]]&"""")),"""")"

        Range("A2").Formula = Built_Formula

    Else
    End If
End Sub

This would rebuild the formula each time you change the value in A1 (e.g. make it a dropdown of those table names).

Or even do the calc in VBA! :)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Lee Taylor

'Excel - Using a cell reference as a TableName in a fully qualified structured reference

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]