Is it possible to use a spill formula to populate a table with Text from multiple worksheets?

[rkaczano]

Hoping to avoid using VBA for this specific task.

I typically add data across worksheets using the following syntax below:

=SUM('<<Start:End>>'!A1)

The idea being that I can add up all the data in all the cell A1s as long the data resides in worksheets that sit inside two worksheets called "<<Start" and "End>>". So if I had 5,10,15,20 in Cells A1 on those sheets SUM('<<Start:End>>'!A1) returns the value 50.

So is it possible to create a dynamic spill formula (or some form of concatenate formula) that uses that same logic to pull text (instead of values) from cell A1 and spill into a dynamic range? Assume I have "cat", "dog", "bird, and "hamster" in cells A1 as described above in between two worksheets called "<<Start" and "End>>". On a separate worksheet I want to pull and display the data as follows using a formula that reads the text data in those cells. The idea hers is that it has to be dynamic so that when I add a worksheet in between '<<Start:End>>'!A1 the formula picks up the data in cell A1 of the new worksheet

SomeFunction('<<Start:End>>'!A1) - pulls text from multiple cells in A1

cat
dog
bird
hamster

Thanks

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=TEXTJOIN(", ",,Start:End!A1)