Hello, I saw similar questions in the following links but none worked for me here
I have 40 sheets with similar headings (or Column titles) and would like to repatriate all the row values into a single sheet without going through the slow process of copying and pasting the rows from each sheet.
thx!
My question is similar to this one, but has additional requirements not addressed there:
[Calc] How to combine multiple tables, in different sheets, into a new one? - Ask LibreOffice
In one file I will many sheets. I will have one main sheet that summarizes and presents all the data from the other sheets. The other sheets will be raw data. I will not organize or format this data. And I cannot change the structure (such as moving or adding columns). Each of these other sheets will have the sam…
My question is similar to this one, but has additional requirements not addressed there:
[Calc] How to combine multiple tables, in different sheets, into a new one? - Ask LibreOffice
In one file I will many sheets. I will have one main sheet that summarizes and presents all the data from the other sheets. The other sheets will be raw data. I will not organize or format this data. And I cannot change the structure (such as moving or adding columns). Each of these other sheets will have the sam…
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) ($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
(Please I have attached the document, I took it from a page I can’t remember the link)Auto_Column_ Combination.ods
