Hopefully I can explain this right.

Looking to combine cells of text strings from multiple worksheets into one master worksheet.

Basically 3-D References. But formatted into rows and columns. And referencing a range of worksheets so new worksheets can be added or removed in between the bookends.

Desired output:

Input: Strings from B1:B3 (should become matching rows separated into columns for each linked worksheet)

Each worksheet ('Worksheet 01:Worksheet 03') follows same format:

Attempts:

=CONCAT('Worksheet 01:Worksheet 03'!B1:B3)

Result:

WS01 Cell B1WS01 Cell B2WS01 Cell B3WS02 Cell B1WS02 Cell B2WS02 Cell B3WS03 Cell B1WS03 Cell B2WS03 Cell B3

Please let me know what you think. Thank you for your time.

CodePudding user response：

=LET(
c,CONCAT(Sheet1:Sheet4!B1:B3),
q,SEQUENCE(LEN(c)/36,3,,12),
TRANSPOSE(MID(c,q,12)))

c uses your CONCAT formula to retrieve a concatenation of all values. q calculates a sequence by the length of c divided by the length of text for the 3 values per Sheet (3* length 12 = 36) by 3 with steps of the length of each value (12). This sequence is used in the MID function and needs the result to be transposed to meet your requirements:

If a Sheet will be added, changing the Sheet names in c will change the result to show the values from that Sheet as well. No further adjustments of the formula are required.

And if the number of outputs per sheet, or string length may change in future you could define these as variables too:

=LET(c,CONCAT('Worksheet 01:Worksheet 03'!B1:B3),
stringlength,12,
stringcount,3,
q,SEQUENCE(LEN(c)/(stringlength*stringcount),stringcount,,stringlength),
TRANSPOSE(MID(c,q,stringlength)))

CodePudding user response：

@P.b just posted a formula approach, but as an alternative here's a VBA user-defined formula which returns an array. The only tricky part is getting the 3D reference in the UDF, since there's no structure or type equivalent to that in VBA: if you try to get it directly from the argument you just get an error.

Building from: https://www.excelforum.com/excel-programming-vba-macros/476283-user-defined-function-receiving-a-range-as-parameter.html

Function MyUDF(v)
    Dim c As Range, f, arr, arrWs, rngAddr
    Dim arrout, indx1, indx2, i As Long, r As Long, data
    
    On Error Resume Next
    Set c = Application.Caller
    On Error GoTo 0
    If c Is Nothing Then
        f = "=myudf(Sheet1:Sheet3!A1:A3)" 'for testing purposes (adjust as needed)...
    Else
        f = c.Formula  'read the formula from the calling cells
    End If
    
    f = Mid(f, 8, Len(f) - 8)   'parse out the parens and formula name
    arr = Split(f, "!")         'get an array from splitting on !
    arrWs = Split(arr(0), ":")  'get the start/end worksheet names
    indx1 = ThisWorkbook.Worksheets(arrWs(0)).Index
    indx2 = ThisWorkbook.Worksheets(arrWs(1)).Index
    rngAddr = arr(1)            '...and the range address
    
    'size the output array
    ReDim arrout(1 To Range(rngAddr).Rows.Count, 1 To 1   (indx2 - indx1))
    For i = indx1 To indx2      'loop over the worksheets
        data = ThisWorkbook.Sheets(i).Range(rngAddr).Value
        For r = 1 To UBound(data)
            arrout(r, i) = data(r, 1)
        Next r
    Next i
    MyUDF = arrout  'return the array
End Function