I'm trying to pull data from cells in a set of other worksheets in the workbook into a summary table. The use case is that the workbook user creates and modifies a series of Details worksheets that are form-like with several cells that contain values such as Name and Age as well as more verbose information that is not needed for the summary.

So far, I've researched the following:

• 3D References
  • This seems useful, but because the users of this workbook will be adding new detail sheets over time, I either have to create empty guard sheets and tell them not to mess with those, or hard code the names of the first and last detail sheets and again, tell them not to mess with those.
  • Regardless, it doesn't appear that you can directly use a 3D reference as the value of a formula. =Joe:John!A2 gives a value error rather than returning three rows.
• UDF Returning a Range
  • The problem I'm running into here is how to actually return the values.
  • I can't just iterate through the sheets and build a Union(Result, CurrentRange) because that line throws a VB error that doesn't break in the debugger which results in an Excel Value Error.
  • I tried storing each of the values in a Collection then converting that to an Array with the first dim being the number of rows and the second dim being 1 column. When I try to set the .Value of the function's return Range, again, VB error as above.
• I have not yet tried having the Function (or Sub in this case?) directly write the values by keeping track of the number of rows and moving through the Summary worksheet cell by cell. This feels really hacky, so I'm hoping it isn't the suggested solution.

I would love to be able to just pass a set of cell references and get a table, but I'd be happy to work with using a separate formula for each column in the summary to specify the value to collate.

I'm not asking for a working solution to be handed off, but I've hit so many dead ends and frustrating errors I'd be very happy with some solid leads. Thank you!

Here are some mockups of the structure of the workbook.

Sheet 1 is named Summary:

The attempts with UDFs I mentioned above were trying to use the following formula in cell B2 on the summary sheet: =GetSummaryData("$D$4")

And here are three example detail sheets:

Joe

Jane

John

Finally, here is some non-functional VBA code from my last attempt:

Option Explicit
Function PersonaSheetsData(RangeRef As String) As Range
    Dim result As Range, thisRange As Range, currSheet As Worksheet, col As New Collection
    For Each currSheet In ActiveWorkbook.Worksheets
        ' Guard to only pull data from Details worksheets
        If currSheet.Cells(1, 1) = "Details" Then
            Set thisRange = currSheet.Range(RangeRef)
            col.Add thisRange.Value
        End If
    Next currSheet
    Set result = ActiveCell
    Set result = result.Resize(col.Count, 1)
    Dim arr As Variant
    arr = collectionToArray(col)
    result.Value = arr
    Set PersonaSheetsData = result
End Function

Function collectionToArray(c As Collection) As Variant
    Dim a() As Variant: ReDim a(0 To c.Count - 1, 0 To 0)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1, 0) = c.Item(i)
    Next
    collectionToArray = a
End Function

CodePudding user response：

This worked for me if you have an "auto-spill array formulas" version of Excel

Function PersonaSheetsData(RangeRef As String)
    Dim result As Range, thisRange As Range, currSheet As Worksheet, col As New Collection
    For Each currSheet In ActiveWorkbook.Worksheets
        ' Guard to only pull data from Details worksheets
        If currSheet.Cells(1, 1) = "Details" Then
            col.Add currSheet.Range(RangeRef).Value
        End If
    Next currSheet
    PersonaSheetsData = collectionToArray(col)
End Function

Function collectionToArray(c As Collection) As Variant
    Dim a() As Variant, i As Integer
    ReDim a(1 To c.Count, 1 To 1) '1-based feels more natural here...
    For i = 1 To c.Count
        a(i, 1) = c.Item(i)
    Next
    collectionToArray = a
End Function