OFFSET function with multiple table

[xdenama]

I have 100++ sheet (table) with same format with difference high (total row not fix, will change). How to combine all table (sheet) in one sheet only. I prefer OFFSET function, any other solution is welcome. TQSM

 

[sandy666]

maybe try Power Query (Get&Transform)

 

[xdenama]

maybe try Power Query (Get&Transform)

How to do?

 

[sandy666]

no example - no details, sorry
but in short: add all tables to the Power Query Editor then use Append feature and result load into the eg. Summarize sheet

btw. update your Account Details about excel version, please

 

[sandy666]

here is an example for few tables

 

[sandy666]

here is the way for

100++ sheet (table) with same format

example:

let
    Source = Excel.CurrentWorkbook(),
    Expand = Table.ExpandTableColumn(Source, "Content", {"Week 1", "Week 2", "Week 3", "Year", "Month", "Day"}, {"Week 1", "Week 2", "Week 3", "Year", "Month", "Day"}),
    Filter = Table.SelectRows(Expand, each not Text.Contains([Name], "Query"))
in
    Filter

and so on...

 

[xdenama]

here is an example for few tables

TQSM, very helpful

 

[sandy666]

TQSM, very helpful

but very annoying for over 100 tables

 

[xdenama]

but very annoying for over 100 tables

What to do...

 

[sandy666]

read post#6

I assumed
- all tables are Excel Tables, not ranges that pretend to be tables
- there is nothing more than these tables

btw. you didn't update your profile about Excel version, can you do that?