Forum Discussion
Pulling table data from many sheets into one table
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.
Thank you for your quick response. As data is sensitive, I have created an example document and shared in the below link.
Sheets 1 - 20 are in the same format as the real document. I have also added a 'Required Output' sheet which shows how the data needs to be extracted and formatted.
Your help would be much appreciated 🙂
https://docs.google.com/spreadsheets/d/1n5tsI966LF2wJepWRqCjnzIMWq3N6dB1/edit?usp=sharing&ouid=102728026850648187467&rtpof=true&sd=true
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"))