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?

 

[alansidman]

Easily achieved using Power Query

See this video-->

 

[Eric W]

It can be done with formulas too:

Book1
	A	B	C	D	E	F
1	Sheets	Rows
2	Sheet2	7
3	Sheet3	6
4	Sheet4	4
5
6
7
8
9
10
11	Sheet2	1		Header	Col2	Col3
12	Sheet2	2		s2a	a	aa
13	Sheet2	3		s2b	b	bb
14	Sheet2	4		s2c	c	cc
15	Sheet2	5		s2d	d	dd
16	Sheet2	6		s2e	e	ee
17	Sheet2	7		s2f	f	ff
18	Sheet3	1		Header	Col2	Col3
19	Sheet3	2		s3a	2a	22a
20	Sheet3	3		s3b	2b	22b
21	Sheet3	4		s3c	2c	22c
22	Sheet3	5		s3d	2d	22d
23	Sheet3	6		s3e	2e	22e
24	Sheet4	1		Header	Col2	Col3
25	Sheet4	2		s4a	3a	33a
26	Sheet4	3		s4b	3b	33b
27	Sheet4	4		s4c	3c	33c
28
29
30
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=MATCH("ZZZZZZZ",INDIRECT("'"&A2&"'!a:a"))
A11:A30	A11	=IFERROR(INDEX($A$2:$A$8,MATCH(ROWS($A$11:$A11),1+(SUBTOTAL(9,OFFSET(B$1,0,0,ROW(B$1:B$8)-ROW(B$1)+1))))),"")
B11:B30	B11	=IF(A11<>"",COUNTIF(A$11:A11,A11),"")
D11:F30	D11	=IF($A11<>"",INDIRECT("'"&$A11&"'!"&ADDRESS($B11,COLUMNS($D11:D11))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Put the list of your sheets in A2:A8. The B2 formula figures out how many rows are on each sheets (based on the last used row in column A). Then the A11 formula duplicates the sheet name the required number of times, and the B11 formula is a row counter. Then the D11 formula get the data from the appropriate sheet and cell. You could add some type of filtering in the A2:A8 cells, depending on if the contents of those cells are constants or formulas.

So yes, there is a formula based approach, but Power Query is probably a better long term solution.

 

[alexcr]

It can be done with formulas too:

Put the list of your sheets in A2:A8. The B2 formula figures out how many rows are on each sheets (based on the last used row in column A). Then the A11 formula duplicates the sheet name the required number of times, and the B11 formula is a row counter. Then the D11 formula get the data from the appropriate sheet and cell. You could add some type of filtering in the A2:A8 cells, depending on if the contents of those cells are constants or formulas.

So yes, there is a formula based approach, but Power Query is probably a better long term solution.

Thanks, @Eric W. Sorry for the dumb question, but how do I download the worksheet/workbook that you inserted into your post?

 

[Fluff]

You just need to click the copy icon & then paste onto A1 of a blank sheet

 

[alexcr]

You just need to click the copy icon & then paste onto A1 of a blank sheet

Ah, got it, @Fluff. But how does that work here, since this is a setup that's supposed to work across multiple worksheets? Unless I'm misunderstanding the approach that @Eric W used.

 

[Fluff]

You will need to set the other sheets up yourself

 

[Eric W]

Right, you didn't say anything about how your sheets looked, so I set up a very generic example. To recreate my example, open a new workbook, and add 4 sheets named Sheet1, Sheet2, Sheet3, and Sheet4. On Sheets 2-4 enter some random data in columns A:C. Make sure the last value in column A in each sheet is a text value. Then go to Sheet1 and paste the formulas from post 3 like Fluff explained. Starting in row 11, you should see the data you entered on Sheets 2-4.

This is just a basic example, and there are a lot of ways to adapt this to your workbook. The values in columns A of the other sheet could be numeric. You could hide columns A:B on Sheet1. The values on Sheet1!A1:A8 could be formulas instead of constants, allowing you to filter. You could drag the D11 formula further to the right to get more columns.

But it all depends on what your workbook looks like, and what your ultimate goal is.

 

[alexcr]

Right, you didn't say anything about how your sheets looked, so I set up a very generic example. To recreate my example, open a new workbook, and add 4 sheets named Sheet1, Sheet2, Sheet3, and Sheet4. On Sheets 2-4 enter some random data in columns A:C. Make sure the last value in column A in each sheet is a text value. Then go to Sheet1 and paste the formulas from post 3 like Fluff explained. Starting in row 11, you should see the data you entered on Sheets 2-4.

This is just a basic example, and there are a lot of ways to adapt this to your workbook. The values in columns A of the other sheet could be numeric. You could hide columns A:B on Sheet1. The values on Sheet1!A1:A8 could be formulas instead of constants, allowing you to filter. You could drag the D11 formula further to the right to get more columns.

But it all depends on what your workbook looks like, and what your ultimate goal is.

Thank you, @Eric W. I've followed your guidance and put together a workbook that replicates what you set up.

I think I understand most of how this is working, with a couple exceptions...

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?

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 .

 

[alansidman]

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