Combine Multiple Sheets

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. Windows
I have a workbook with sheets for each financial year from 2010 onwards. All column headers are the same across all sheets.

I need to be able to combine all the sheets into one sheet *BUT* I need this combined sheet to change with the others if rows are added/deleted in any of the other sheets. I did play around with power query but I couldn't get anything that works the way I want it to.

I've been playing around with VSTACK but if I include blank rows in the range, they return as "0" in the combined sheet. I need the sheet to have all data in one block, not with loads of blank rows included in-between all the data, example:

1695763087163.png


Rows 15-30 are blank in the first sheet, but they return 0's in the VSTACK (column A is custom date formatted).

How do I get the formula to ignore blank rows, so that row 31 would be at row 15?

Thanks
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You could use =FILTER(), and exclude where columns C, D or E = 0, or column B if you want to include the "N/A" employers.

Book1
ABCDEFGHIJK
1Payroll InformationPayroll Information
2Pay MonthEmployerTax CodePay FrequencyWeekly Paid HoursPay MonthEmployerTax CodePay FrequencyWeekly Paid Hours
310-AprN/A0045148Howden Joinery647PAYE (Monthly)44
410-MayN/A0045179Howden Joinery647PAYE (Monthly)44
510-JunN/A0045209Howden Joinery647PAYE (Monthly)44
610-JulN/A0045240Howden Joinery647PAYE (Monthly)44
710-AugHowden Joinery647PAYE (Monthly)4445270Howden Joinery647PAYE (Monthly)44
810-SepHowden Joinery647PAYE (Monthly)4444937Howden Joinery647PAYE (Monthly)44
910-OctHowden Joinery647PAYE (Monthly)4444968Howden Joinery647PAYE (Monthly)44
1010-NovHowden Joinery647PAYE (Monthly)4444996Howden Joinery647PAYE (Monthly)44
1110-DecHowden Joinery647PAYE (Monthly)4445027Howden Joinery747PAYE (Monthly)44
1211-JanHowden Joinery647PAYE (Monthly)4445057Howden Joinery747PAYE (Monthly)44
1311-FebHowden Joinery647PAYE (Monthly)4445088Howden Joinery747PAYE (Monthly)44
1411-MarHowden Joinery647PAYE (Monthly)4445118Howden Joinery747PAYE (Monthly)44
15Jan-000000
16Jan-000000
17Jan-000000
18Jan-000000
19Jan-000000
20Jan-000000
21Jan-000000
22Jan-000000
23Jan-000000
24Jan-000000
25Jan-000000
26Jan-000000
27Jan-000000
28Jan-000000
29Jan-000000
30Jan-000000
3111-AprHowden Joinery747PAYE (Monthly)44
3211-MayHowden Joinery747PAYE (Monthly)44
3311-JunHowden Joinery747PAYE (Monthly)44
3411-JulHowden Joinery747PAYE (Monthly)44
Sheet2
Cell Formulas
RangeFormula
G3:K14G3=FILTER($A$3:$E$34,$C$3:$C$34<>0)
Dynamic array formulas.
 
Upvote 1
Are all the sheets you want to combine consecutive?
If so you could use something like
Excel Formula:
=LET(v,VSTACK('2010:2023'!A2:F100),FILTER(v,INDEX(v,,2)<>""))
 
Upvote 1
Solution
Are all the sheets you want to combine consecutive?
If so you could use something like
Excel Formula:
=LET(v,VSTACK('2010:2023'!A2:F100),FILTER(v,INDEX(v,,2)<>""))
Thanks again Fluff! With a little tweaking, it works perfectly :)

You really are a great help with excel and I really appreciate it
 
Upvote 0
You could use =FILTER(), and exclude where columns C, D or E = 0, or column B if you want to include the "N/A" employers.

Book1
ABCDEFGHIJK
1Payroll InformationPayroll Information
2Pay MonthEmployerTax CodePay FrequencyWeekly Paid HoursPay MonthEmployerTax CodePay FrequencyWeekly Paid Hours
310-AprN/A0045148Howden Joinery647PAYE (Monthly)44
410-MayN/A0045179Howden Joinery647PAYE (Monthly)44
510-JunN/A0045209Howden Joinery647PAYE (Monthly)44
610-JulN/A0045240Howden Joinery647PAYE (Monthly)44
710-AugHowden Joinery647PAYE (Monthly)4445270Howden Joinery647PAYE (Monthly)44
810-SepHowden Joinery647PAYE (Monthly)4444937Howden Joinery647PAYE (Monthly)44
910-OctHowden Joinery647PAYE (Monthly)4444968Howden Joinery647PAYE (Monthly)44
1010-NovHowden Joinery647PAYE (Monthly)4444996Howden Joinery647PAYE (Monthly)44
1110-DecHowden Joinery647PAYE (Monthly)4445027Howden Joinery747PAYE (Monthly)44
1211-JanHowden Joinery647PAYE (Monthly)4445057Howden Joinery747PAYE (Monthly)44
1311-FebHowden Joinery647PAYE (Monthly)4445088Howden Joinery747PAYE (Monthly)44
1411-MarHowden Joinery647PAYE (Monthly)4445118Howden Joinery747PAYE (Monthly)44
15Jan-000000
16Jan-000000
17Jan-000000
18Jan-000000
19Jan-000000
20Jan-000000
21Jan-000000
22Jan-000000
23Jan-000000
24Jan-000000
25Jan-000000
26Jan-000000
27Jan-000000
28Jan-000000
29Jan-000000
30Jan-000000
3111-AprHowden Joinery747PAYE (Monthly)44
3211-MayHowden Joinery747PAYE (Monthly)44
3311-JunHowden Joinery747PAYE (Monthly)44
3411-JulHowden Joinery747PAYE (Monthly)44
Sheet2
Cell Formulas
RangeFormula
G3:K14G3=FILTER($A$3:$E$34,$C$3:$C$34<>0)
Dynamic array formulas.
Thanks for taking the time to respond dreid, really appreciate it.

Fluff's response was the one that worked. It basically allows me to select 300 rows on each sheet and won't include any blank rows in the data. Thus, then I can add something later on onto one of the sheets, it appears in "all data".

Hope that makes sense.

Formula is:
Excel Formula:
=IF(LET(v,VSTACK('FY 2010-11:FY 2026-27'!A3:AD300),FILTER(v,INDEX(v,,2)<>""))=0,"",LET(v,VSTACK('FY 2010-11:FY 2026-27'!A3:AD300),FILTER(v,INDEX(v,,2)<>"")))

The If formula then stops it returning "0" for blank cells.

Many Thanks
 
Upvote 0
If those cells actually contain a 0 on the original sheets then you can simply use
Excel Formula:
=LET(v,VSTACK('FY 2010-11:FY 2026-27'!A3:AD300),FILTER(v,INDEX(v,,2)<>0))
 
Upvote 1

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top