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

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


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