We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
1

[Calc] How to combine multiple tables, in different sheets, into a new one? [closed]

asked 2014-03-17 18:27:44 +0200

C.Kai gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:21:21.582528

1 Answer

Sort by » oldest newest most voted
1

answered 2014-03-17 23:38:01 +0200

m.a.riosv gravatar image

updated 2014-03-17 23:38:56 +0200

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)))

JoinDataFromSeveralSheets_20140317.ods

edit flag offensive delete link more

Comments

1

Wow! You even make an example for this question! That exact what I need. Thanks a lot!

C.Kai gravatar imageC.Kai ( 2014-03-18 17:25:30 +0200 )edit

It helped me too :D

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 :-)

libre officer gravatar imagelibre officer ( 2020-10-07 19:56:03 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-03-17 18:27:44 +0200

Seen: 5,172 times

Last updated: Mar 17 '14