Combine Multiple Sheets to One and Perform Calculation

[cyliyu]

I have a workbook with several sheets organized by month/year (Nov23, Dec23, Jan24, etc.).
Column N will have a totaled value at the end of last row.

I'd like to consolidate these various sheets into a single sheet called MasterSheet so that as new months are introduced, I only have to maintain one MasterSheet.
I have a Dashboard sheet that will summarize the sum of based on the year/month.
How can I have the Dashboard sheet to check for Year/Month (From MasterSheet) and provide the sum automatically?

Please see attached file the sample file.

[AliGW]

Are you still working with Excel 2016?

[cyliyu]

Yes. I am still using Excel 2016 and going to purchase Office 2019 or 365 when I buy a new laptop next month.

[AliGW]

Get 365 - it's much better and will make things like this really simple.

So, to summarise: you want the entire dashboard table to auto-generate - is this it?

[cyliyu]

Okay, I will purchase 365. Thanks.
Yes, I want the dashboard to be automated.

[AliGW]

Okey-dokey. Not so easy with Excel 2016, but I'll have a look (as I am sure will others here).

[paulma1960]

Sounds to me like an easy job with Power Query, and I agree with Ali that Excel 365 is so worth it.

[cyliyu]

How can I do it in power query?

[AliGW]

Will that total cell always be in the same place on every sheet?

[AliGW]

Here's a formula solution.

In Name Manager, create an item called SheetNames:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Then, in C6:

=ROWS(C$6:C6)

and in D6:

=2000+INDEX(RIGHT(SheetNames,2),C6+2)

and in E6:

=INDEX(LEFT(SheetNames,3),C6+2)

and in F6:

=SUMIF(Table26[Month],[@Month],Table26[Total Consumption])

[cyliyu]

Thanks, AliGW.
It solved.

I suppose the same formula can also be used in 365 or 365 will have another formula to perform the same function. Thanks you so much.

[cyliyu]

By the way, I just realized that 2023 and 2024 are combined in the Total Consumption calculation. i.e., the data for December 2023 and December 2024 will be combined rather than displayed as separate rows.

[AliGW]

OK - so provide a more rea;istic sample dataset and I'll have a look again for you.

[AliGW]

You could see if this works:

=SUMPRODUCT((Table26[Month]=[@Month])*(YEAR(Table26[Date])=[@Year])*IF(Table26[Total Consumption]="",0,Table26[Total Consumption]))

[cyliyu]

Thanks, AliGW.

[AliGW]

No problem.