Forum Discussion
VSTACK, INDIRECT, and BYROW to pull in data from many sheets referencing a range of cell addresses
(update - attached file) Hi all! I have a unique problem which is a result of having to create a large "output" range from multiple sheets. I'm building a large "demo data" bank, but the res...
I would imagine there's a much better set-up without all this volatility. Since you're in any case already using some VBA, why not simply add a procedure to perform your required consolidation?
That said, here's one possibility:
=LET(
ζ,A1:A2,
TOCOL(CELL("contents",
OFFSET(INDIRECT(ζ),SEQUENCE(,ROWS(INDIRECT(TAKE(ζ,1))),0),)
)
)
)
A1:A2 can be amended as required. And this can of course then be copied to the right.
JosWoolley - thanks! That didn't quite work for me. I made an example sheet to illustrate. Would greatly appreciate the help!
Can you explain how it "didn't work" for you? I can't see any attempt in that workbook of you trying to implement my solution. I replaced A1:A2 with I6:I8 in the formula I provided and it gave the exact same results as yours in I13:I42.
- Apologies. Before trying again - is the "zeta" symbol in your formula above just a placeholder for any letter? Thanks!
You can rename it to whatever you like if you wish.