4

I have lots of sheets describing different kind of expenses and gains of my small company, and I find no easy way to merge my tables like in this example I made:

Merging tables in Google Sheets goal

I want the last table to be auto filled with the lines of the others tables when I update them, so I can foresee the expenses and gain in time (by ordering the green table automatically by date ascending).

By now the only temp solution I found is to copy references to the other tables lines (yellow and blue) in the merging table (green) in advance.

Pivot tables do not permit to achieve this kind of gathering on several tables.

Community
  • 1
  • 1
Ty Kayn
  • 719
  • 7
  • 24
  • Why the excel tag? If you were using Excel then there may be A VBA solution. Excel 2013+ may allow a PowerPivot answer. Google Sheets don't implement either of those. – Mark Fitzgerald Dec 28 '16 at 14:47
  • ok well, i am looking for a solution who would work in any calc software, but my preferences goes to libreoffice/openoffice/google sheets ;) i tagged excel to look for similar solutions in these – Ty Kayn Dec 28 '16 at 14:50

1 Answers1

7

Use this Query formula in cell I2:

=QUERY({A2:C; E2:G}, "select * where Col1 is not null", 1)

To also order them by Date, add the order by:

=QUERY({A2:C; E2:G}, "select * where Col1 is not null order by Col1", 1)
Karl_S
  • 3,364
  • 2
  • 19
  • 33
  • we are going somewhere with this, but i get a syntax error. i finally arrived to get something good with this QUERY function. like so: =QUERY({E2:G;A2:C}; "select * where Col1 is not null") thanhks! – Ty Kayn Dec 28 '16 at 15:42