Hi Experts,
How can I combine multiple tables, in different sheets, into a new one?
(without spend long time on copy paste~)
ex: There are 26 data sheets in my file, Apple, Ball, Cell, Diary… Zebra.
I want to combine all A2:A10 in every sheet into a new table.
Thanks
Please see if attached file is what you are looking for.
With the list of the sheet names we can compose the address to use with indirect to get values for every sheet.
=INDIRECT(ADDRESS(MOD(ROW()-2;9)+2;COLUMN();1;1;OFFSET($E$2;INT((ROW()-2)/9);0)))
Wow! You even make an example for this question!
That exact what I need. Thanks a lot!
It helped me too 
I just modified $E$2 to match list of my sheets names, and replaced every 2 and 9 respectively with the location of my 1st row and my last row-1
Veeery helpful 