Excel: Accumulated data from other sheets that grows as each supplier sheet grows

[ShadowRider]

I asked a similar question for google sheets in another forum and have not yet been told if its possible there so I thought I'd see if it might be possible with Excel:

Given two similar sheets with data that grow over time, it is possible to have a third sheet that grows with all the data from the first two?

Something like:

Sheet 1with single column data of A, B, and C and Sheet 2 with single column of X, Y, Z,
Have a third sheet that would contain A,B,C,X,Y,Z and would grow to included new rows of sheets 1 and 2.

Thanks,
-Eric

PS - I have access to Excel for MAC version 16.80; Microsoft 365 subscription.

 

[Joe4]

Though all things equal, I might opt for a database solution to a problem like that, it should be absolutely possible with Excel.
Of course, the devil is always in the details. I would recommend showing some examples of your data and expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

PS - I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). Then you do not have to type it into each question, as it will show up under your profile picture to the left of your posts.

 

[ShadowRider]

Under Google sheets, I was able to use sort, arrayformula and query to get the results I was looking for.

 

[ShadowRider]

I would like to get this working under Excel.

Here is my data (I was unable to get xl2bb to copy from multiple sheets so I did them separately):

Book2.xlsx
	A	B	C	D	E	F
1	Date	Type	Category	Amount	Details	Source
2	1/2/24	Expense	Entertainment	$100	ATM	Sheet1
3	1/15/24	Expense	Dining Out	$50	Pizza	Sheet1
4	1/20/24	Payment	Payment	$200	Dec payment	Sheet1
Sheet1

Book2.xlsx
	A	B	C	D	E	F
1	Date	Type	Category	Amount	Details	Source
2	1/2/24	Expense	Maintenance	$100	Sink	Sheet2
3	1/15/24	Expense	Gas	$50	BP	Sheet2
4	1/21/24	Payment	Payment	$300	Dec payment	Sheet2
Sheet2

Book2.xlsx
	A	B	C	D	E	F
1	Date	Type	Category	Amount	Details	Source
2	1/1/24	income	paycheck	$1,000	payday	Sheet3
3	1/15/24	income	paycheck	$1,000	payday	Sheet3
Sheet3

Book2.xlsx
	A	B	C	D	E	F
1	Date	Type	Category	Amount	Details	Source
2	1/1/24	income	paycheck	$1,000	payday	Sheet3
3	1/2/24	Expense	Entertainment	$100	ATM	Sheet1
4	1/2/24	Expense	Maintenance	$100	Sink	Sheet2
5	1/15/24	Expense	Dining Out	$50	Pizza	Sheet1
6	1/15/24	Expense	Gas	$50	BP	Sheet2
7	1/15/24	income	paycheck	$1,000	payday	Sheet3
8	1/20/24	Payment	Payment	$200	Dec payment	Sheet1
9	1/21/24	Payment	Payment	$300	Dec payment	Sheet2
Sheet4

With google sheets, I used:

=sort(arrayformula(
query(
{ 'sheet1'!A2:F, iferror('sheet1'!A2:A/0, 'sheet1'!A1);
'sheet2'!A2:F, iferror('sheet2'!A2:A/0, 'sheet2'!A1);
'sheet3'!A2:F, iferror('sheet3'!A2:A/0, 'sheet3'!A1)
},
"where Col1 is not null", 0 )
),1,1)

How can I get Sheet 4 results in Excel? (Sheets 1,2,3 grow over time and I'd like the results to grow as well)

 

[Fluff]

How about

=LET(v,VSTACK(Sheet1:Sheet3!A2:F500),SORT(FILTER(v,INDEX(v,,1)<>"")))

Sheets 1,2 & 3 need to be consecutive in the workbook

 

[ShadowRider]

How about

=LET(v,VSTACK(Sheet1:Sheet3!A2:F500),SORT(FILTER(v,INDEX(v,,1)<>"")))

Sheets 1,2 & 3 need to be consecutive in the workbook

Yes! That works nicely. Thanks!

 

[mumps]

Name your destination sheet as "Summary". This code will automatically adjust to a varying number of sheets.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Summary")
    desWS.UsedRange.Offset(1).ClearContents
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            ws.UsedRange.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next ws
    desWS.Cells(1, 1).Sort Key1:=desWS.Columns(1), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback