Puzzle: How to easily append 10+ columns of data from different sheets

[rayd3601]

this function generates an array for column A and skips any blank fields.

=IFERROR(INDEX('Sheer(1)'!$A$1:$A$50,SMALL(IF($A$1:$A$50<>"",ROW($A$1:$A$50)),ROW(A1))),"")

This function is the first function then appends another sheet to the end of the array

=IFERROR(INDEX('Sheet(1)'!$A$1:$A$50,SMALL(IF($A$1:$A$50<>"",ROW($A$1:$A$50)),ROW(B17))),IFERROR(INDEX('Sheet(2)'!$A$1:$A$50,SMALL(IF($A$1:$A$50<>"",ROW($A$1:$A$50)),ROW(B17)-COUNTA($A$1:$A$50))),""))

it appends the second sheet and adjusts the count for the small(). if i have 10 sheets then this function becomes too long and i may end up having 20 or more sheets eventually.

is there an easier way to ingeniously use the indirect() and naming cells to make this process easier? i feel like there is but i cannot figure it out

 

[Nebu]

Hi:

I am not sure formula is a good idea to consolidate data across sheets if the nof sheets are going to increase in the future, instead try the macro attached.

Note:

I have done it for just one column, if you are ok with VBA , I can extend it to rest of the columns.

Thanks