Is there a formula to pull all the rows from multiple worksheets onto a single worksheet?

[alexcr]

Let's say you have multiple worksheets with data, and they're each regularly changing, i.e. rows being added, rows being deleted, rows being edited, etc..

You want to have a single worksheet that pulls together the rows from all of these worksheets, and that automatically updates when any of the worksheets change. It would be ideal if you could dictate the order of the rows being pulled in, i.e. the rows from Worksheet A would be followed by the rows from Worksheet B, etc.. It would also be ideal if you could add in filters, if necessary, for the rows being pulled in, e.g. to only include rows from the worksheets that meet certain criteria.

Any recommendations on how to go about this using a formula-based approach?

 

[alexcr]

Did you look at the tutorial I provided? You have not commented on that.

Sorry for not commenting. I appreciate your sharing the tutorial. I was looking for a formula-based solution, though. Not looking to dig into Power Query at this point!

 

[Eric W]

1) Cells B2-B4. I get that these functions count how many rows are in each worksheet, but what's the significance of the "ZZZZZZZ"in these functions?

The LOOKUP is looking for a particular value. If it doesn't find it, it'll keep looking until the end of the range. When looking at a text column, ZZZZZZZ should be after anything in the column, so it won't find anything, and will just return the last cell with data in it. If you're looking at a column with numbers instead of text, use a very big number instead.

2) Cells B11-B20. I'm totally lost on how these functions work. If it's too complicated to explain, no worries, I'll just put my trust in them .

The B11 formula is actually pretty simple, but it does rely on a trick you might not have seen before.

=IF(A11<>"",COUNTIF(A$11:A11,A11),"")

Notice that the range A$11:A11 has a $ sign before the first 11, but not before the second 11. This means that as you copy the formula down the column, the first 11 stays fixed, but the second one increases. A11:A11, A11:A12, A11:A13, etc. So for each formula down the column, the range increases to include everything down to the current row. Then COUNTIF counts how many times the value in A11 is in that range, which will be the number of times the sheet name has been displayed so far, so it can be used as a row number.

Hope this helps!

 

[alexcr]

The LOOKUP is looking for a particular value. If it doesn't find it, it'll keep looking until the end of the range. When looking at a text column, ZZZZZZZ should be after anything in the column, so it won't find anything, and will just return the last cell with data in it. If you're looking at a column with numbers instead of text, use a very big number instead.

Thanks, @Eric W! I didn't realize the lookup works alphabetically. That makes a lot more sense.

The B11 formula is actually pretty simple, but it does rely on a trick you might not have seen before.

I actually got the part. Sorry for not being more specific. The part I'm totally lost on is...

1+(SUBTOTAL(9,OFFSET(B$1,0,0,ROW(B$1:B$8)-ROW(B$1)+1))))),"")

 

[Eric W]

Yeah, I was a bit surprised when you said you didn't get the B11 formula, but didn't say anything about the more complicated A11 formula.

In order to decide how many times to display each sheet name in A11-A30, we use the MATCH function, using ROWS($A$11:$A11) as a counter. The counter goes from 1, 2, 3, etc. In my previous example, Sheet2 as 7 rows, Sheet3 has 6 and Sheet4 has 4. If we can set up an array of {1,8,14} and use MATCH with the counter, MATCH will return {1,1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3}. Any value of the counter from 1-7 will match the first element, 8-13 will match the second element, 14-up will match the third. Give those values to INDEX and we have our list.

So how do we get our {1,8,14} array? If you subtract 1 from each element, you get {0,7,13}. The first element is 0, the second element is the number of rows in Sheet2, the third element is the number of rows in Sheet2+Sheet3, and if we had more sheets, we'd just keep adding the number of rows to it. Thus our array is
SUM(B1:B1) (B1 being non-numeric will return 0), SUM(B1:B2), SUM(B1:B3), SUM(B1:B4), etc.

Now we get to the OFFSET part. In OFFSET(B$1,0,0,h), the h is the height of the range, so starting at B$1, if we can change the height from 1 to 2 to 3, etc. we're there. In an array formula ROW(B$1:B$8)-ROW(B$1)+1 gives us the {1,2,3...} array. (If you have the new array functions in Excel 365, SEQUENCE(8) would be preferred.) So OFFSET has now given us the B1:B1, B1:B2, B1:B3, etc. ranges. However, OFFSET is kind of finicky, it doesn't work in a lot of array formulas. But you can use OFFSET inside certain other functions, inside array formulas. An array OFFSET won't work in the SUM function, but it will in the SUBTOTAL function, which you can set to be SUM (option 9).

So ROW(B$1:B$8)-ROW(B$1)+1 gives us a 1-8 sequence, OFFSET gives us the B1:B1, B1:B2, etc. set of ranges, SUBTOTAL gives us the totals, we add 1, then we use MATCH to find the right sheet name.

See? Piece of cake! ?

Hope this helps!

 

[alexcr]

Hope this helps!

@Eric W, I appreciate you taking the time to explain this. It actually does make sense now conceptually, though I'll have to play around with the formula some more so that the mechanics of each part sink in.

Thanks again for your help!

 

[albertof91]

Hello everyone!

Even this question was posted almost 2 years ago, you can't imagine how much it has helped me.
Do you guys have any recommendation in case the information in the sheets are not in cell A1, but maybe identify them in another column by title and then count the rows below that to have the same result?

 

[Eric W]

Wow, I'm lucky if I can remember what I had for breakfast, let alone something from 2 years ago!

So you have multiple sheets, each with headings (for example) of Name, Address, Age, Job, etc. and you want to list everything under the Address heading from each sheet? Will the column be the same on each sheet? Are you looking to list a single column, or several?

Microsoft is working on some new functions, like VSTACK, that would make it much easier to do this. They're not quite ready yet, but I can probably come up with something like what I did before.

 

[albertof91]

Wow, I'm lucky if I can remember what I had for breakfast, let alone something from 2 years ago!

So you have multiple sheets, each with headings (for example) of Name, Address, Age, Job, etc. and you want to list everything under the Address heading from each sheet? Will the column be the same on each sheet? Are you looking to list a single column, or several?

Microsoft is working on some new functions, like VSTACK, that would make it much easier to do this. They're not quite ready yet, but I can probably come up with something like what I did before.

Dear Eric,

I really appreciate your reply! Let me try to clarify the issue I have in a bit more detail.

All the different sheets I have in my file (more than 400 sheets) are "purchasing orders" for different suppliers, in the image below (let's say the sheet's name is "OC1" or "sheet2"), in columns B, C, D & H, I´ve marked in yellow the titles/information which I would like to retrieve in a summary sheet. Next sheet "OC2/sheet3" would have the same columns, but different ammount of rows, since it would be a different supplier or products purchased.

My goaI is to have a summary (image below as reference) that retrieves "Cant" = quantity, "Unidad" which are units, 3rd column "descripción" which is description, etc. to get a complete consolidation of all the different sheets and build a data base for internal analysis of all purchased material.
I'm struggling on how to "skip" all the rows before the titles marked in yellow since some of them have text and also limit the formula to skip the rows below the cells marked in yellow.

Summary (reference):

I could edit the different sheets to delete rows 19-21 which are almost blank cells between titles and required information, but I as I mentioned l don't know how to limit the formula to only get the information of the purchased products and don't get the info from rows 31 and above.

Let me know if I've explained myself correctly.
Thank you again!