merge multiple sheets into single sheet

asked 2019-03-03 08:29:18 +0100

bunmalik gravatar image

Hello, I saw similar questions in the following links but none worked for me here

https://ask.libreoffice.org/en/questi... https://ask.libreoffice.org/en/questi... https://ask.libreoffice.org/en/questi...

What have now:

Define List_1 and List_2 in Sheet1 and Sheet2 as follows:

List_1 = OFFSET($Sheet1.$A$2;;;SUMPRODUCT(MAX(ROW($A$1:$A$5000)($Sheet1.$A$2:$A$5001<>"")));1) List_2 = OFFSET($Sheet2.$A$2;;;SUMPRODUCT(MAX(ROW($A$1:$A$5000)($Sheet2.$A$2:$A$5001<>"")));1)

Then in Sheet3 type this formula and drag fill

=IF(ROW(E1)>ROWS(List_1)+ROWS(List_2),"",IF(ROW(E1)<=ROWS(List_1),IF(INDEX(List_1,ROW(E1))="","",INDEX(List_1,ROW(E1))),IF(INDEX(List_2,ROW(E1)-ROWS(List_1))="","",INDEX(List_2,ROW(E1)-ROWS(List_1)))))

It works perfectly but it's only for two sheets. Please could someone help me extend the formula for more than two sheets Say adding Sheet4 with the List_3 = OFFSET($Sheet4.$A$2;;;SUMPRODUCT(MAX(ROW($A$1:$A$5000)*($Sheet4.$A$2:$A$5001<>"")));1) is added. How do I add it to the IF statement?

(Please I have attached the document, I took it from a page I can't remember the link) The full explanation is in the file C:\fakepath\Auto_Column_ Combination.ods

edit retag flag offensive close merge delete