Index Multiple Sheets

[INKLO708]

hi there, i am stucked in a situation where i have 4 sheets and 1 as final sheet. i am using formula like =IFERROR(INDEX(PAINT!$D$7:$I$407,PAINT!$C14,COLUMNS($B$7:E14))," ")
for one sheet indexing but my question is what formula should be there for taking all 4 sheets data in final ??
in other words, how to get data of all sheets in final sheet with the help of index.???????

 

[sandy666]

my suggestion was about Power Query not about worksheet formula or function, these are two very different things
I don't know how to do that with formula

 

[INKLO708]

okay i got your point.
but i can not use power query due to few automation and filtered option in my final sheet. thats y i need formula base solution.
any idea plz share thanks

 

[sandy666]

due to few automation and filtered option in my final sheet. thats y i need formula base solution.

all you can do with Power Query (theory because I don't know what you want to achieve) or you can use any formula for final table but not inside this table because after refresh whole table will be back to original values
the rest is up to you

Append queries (Power Query) - Microsoft Support

In Power Query, the Append operation creates a new query that contains all rows from a first query followed by all rows from a second query.

support.microsoft.com