Combine multiple tables in multiple sheets

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hello hoping someone can help me.
I work for a bus company. They have a fleet of shuttle busses, charter busses and Tour busses. In multiple towns.
They want each office to track passengers on and off and luggage on and off.
So I created 3 different tables. One for each type of bus.
The headers are column 2 "passengers on", column 3 "passengers off", column 4 "bags on", column 4 bags off. Column 1 has the bus numbers. Like "255" 255 is a shuttle bus so it goes in the shuttle table. I created 31 sheets with each sheet having the 3 tables in them. The problem im having is that the owners want a total for each bus number not just a total for the type of bus. What im looking of is a way to have a totals sheet which would add the 3 tables in sheets 1 through 31 and give me the 3 tables with a combined total in them. Another hiccup is that sometimes they will do special busses so rows will be added or deleted on a daily basis. Any ideas on how to do this? I've tried power query which is super complicated to me even after watching multiple videos and I've tried the consolidate button in data tools but it adds random columns and doesn't update correctly even though I've checked the create link to source data. I'm hoping maybe a VBA code or something thats a running total kinda of thing because this workbook will be going to different offices and not everyone knows what excel is lol.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

aRandomHelper

Board Regular
Joined
Jan 14, 2021
Messages
200
Office Version
  1. 2016
Platform
  1. Windows
I think you should be able to pull it off with a 'Master' sheet containing the 3 tables, each with every sheet's Column 1 populated, then do a Sumif formula for each bus number? This way, this Master sheet will always be updated as you update the remaining 31 sheets.

If you're strictly looking for a VBA solution, you probably need to attach a sample workbook with all the tables and whatnot; no one knows how your tables look like or how they are arranged, in which sequence are your bus data plotted, etc.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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
Top