Hi there.


I'm trying to summarize the table on Sheet2 that covers 12 tables in a single sheet with the necessary data. I have included only 2 tables, deleting the remaining.

Kindly assist.

Also, I would like to know if my issue can be solved via power query.

Attached Files

BRR.xlsx (30.6 KB, 10 views)

You make here a typical mistake of many Excel users! You are handling your data as some bunch of reports - like paper reports from 100 years back - instead handling them as a database.

Instead you must have a single of data entry table (or a couple of data entry tables on separate sheets in case you can't put all data into single table) - with columns for year and month, or even better a column with month in format "yyyymm". Instead of current monthly tables you must have a single report sheet (designed like your Sheet2), where you select a month (together with year), and get months data read from entry sheet(s). And another sheet (designed like your Sheet1), where totals of every month are calculated from entry sheet(s).

Probably you need also some table(s) where some constants used in calculations are stored.

As result, you get a workbook with 1-3 data entry sheets, and with a couple of report sheets, instead of 12+ sheets, and this workbook will work for many years without any need for redesign, unless there are some major changes in your data structure.

Sir, are you trying to convey that each month data be in different sheet?

Not to mentions dozens of merged cells...

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

On the contrary! Data for any month are entered into same table on same sheet! Only acceptable reason to have more entry sheets is part of entry data having different structure.

An example for need of several entry sheets may be e.g. Invoices handling workbook, where one entry sheet contains general invoice info (invoice number, invoice date, client name/id, etc.), and another entry sheet contains invoice details/row info (invoice number, invoice row number, article code, article unit price, quantity, etc.). And such workbook can have quite a number of various reports, like monthly/yearly totals, monthly/yearly totals for certain client, monthly/yearly totals for certain article, etc. Your imagination and skills, and the number of parameters your tables contain, are only limitations.