Forum Discussion
Solved
Pulling table data from many sheets into one table
Hi Everyone, I have an excel workbook containing 20 sheets (named 1 through to 20). Each one of these sheets contains data in columns B - AB and rows 14 - 250. I want to pull columns D &...
Patrick, you're way ahead of me in grasping those functions. (VSTACK in particular is one I have never used; not even sure I've seen it in operation before).
It seems to me that the one thing your formula is missing, however, is Serdet 's original request that it pick up the data from those subordinate sheets when column A (in this case) has the value "3m"
I've taken the liberty of changing a few of the values in the attached revision of your sheet so as to verify that different data are coming from different sheets as desired. But I'd like to see how the formula gets revised to pick up that "3m" criterion originally specified.
If you have 365, perhaps this:
=LET(stack,VSTACK('1:20'!$A$14:$AB$20),reference,TAKE(stack,,1),FILTER(CHOOSECOLS(stack,4,28),reference="3m"))
Patrick, you're way ahead of me in grasping those functions. (VSTACK in particular is one I have never used; not even sure I've seen it in operation before).
It seems to me that the one thing your formula is missing, however, is Serdet 's original request that it pick up the data from those subordinate sheets when column A (in this case) has the value "3m"
I've taken the liberty of changing a few of the values in the attached revision of your sheet so as to verify that different data are coming from different sheets as desired. But I'd like to see how the formula gets revised to pick up that "3m" criterion originally specified.
- Good catch. It doesn't affect the sample in this case but I've updated my post to include that criteria.
- mathetes Patrick2788
Thank you very much for helping me with my problem.
I have revised the formula to pick up "3m" in column B by using the below:
=LET(stack,VSTACK('1:20'!$B$14:$AB$250),FILTER(CHOOSECOLS(stack,1,3,25),TAKE(stack,,1)="3m"))
