Efficient Summing Across Tabs

[mountainman88]

I have inherited a huge financials file with hundreds of country P&L tabs which pull from a data table. These country tabs are then summarised by identically laid out region tabs using a basic sum function linking to the relevant cells on the country tabs

Eg. EMEA region tab cell A1 = Sum(France!a1,Germany!a1,Italy!a1)

Using this method if i was to add another country to EMEA it would be very cumbersome to update every formula to for example = Sum(France!a1,Germany!a1,Italy!a1,Denmark!a1)

Ive tried using 3DSUM and SUMPRODUCT methods but they slow the sheet down too much. Can any one think of a way to update the SUM functions easily for additional country tabs added to a region?

Thanks

 

[mountainman88]

A agree with the database suggestions.
If the data is not too extensive, you may be able to use one Data sheet and a Report page.
With good data, you could use Pivot Tables, Filters, Excel formulas, etc.

or
You can consider the following that uses a named list of relevant sheets.

3d Sumif 2021.xlsm
	A
10	12
11	12
Summary

Cell Formulas
Range	Formula
A10	A10	=SUMPRODUCT(N(INDIRECT("'"&Sheets&"'!A1")))
A11	A11	=SUMPRODUCT(N(INDIRECT("'"&Sheets&"'!"&CELL("address",A1))))

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$F$2:$F$4	A10:A11

I’ve used something like this before and unfortunately it will be too slow in the workbook I’m using. I tried this method and 3DSum with slow results.

Is there some VBA I could run that updates all the ‘total’ tabs based on a list of tabs it needs to sum.

Maybe I just click a button for that and it’s quicker?

Thanks