Avoiding INDIRECT when referencing and comparing ranges in different workbooks

[HeadyFinance]

I work in Finance and have a QoL-issue with excelling daily.

I use INDIRECT and SUMIFS constantly when comparing figures. Oftentimes it's about comparing latest estimates with previous forecasts or budgets in an earlier instances of the same file (although with a different name).

'Criteria_Range' is a named date row of months (2022-01-01 for Jan, 2022-02-01 for Feb, etc). 'Total_Revenue' is a named revenue row. So essentially what I am doing in the attached picture is summing up the budgeted revenue for 2022 and the forecasted revenue for 2022. It works wonders for quickly comparing figures between workbooks, but it gets noticeably slowed down as I add more items in, say, a P&L statement.

My business sells goods in different channels, all with their separate monthly P&Ls and forecasts in respective sheets, all linking to a master three-statement sheet in a workbook.

Sample of the master three-statement sheet:

These are the first rows of the sheet. It's a P&L, then Cash Flow Statement, then Balance Sheet. Row 4 is the 'Criteria_Range', row 15 is the 'Total_Revenue' range. I've named the other rows as well, so if I would like to see only Online Net Revenue, I would use 'Online_Revenue' instead of 'Total_Revenue' in cell A4 of the first image.

Whenever I budget I save a separate file to keep all data intact, so future comparisons to budget can be done on a very granular level, and for varying spans of time.

The comparisons are very much variable, so it's really handy to have a big set of named ranges for the rows so that I can quickly map out whatever I want to. It's a big, complex file that has several external .csv files linked into it through power query to minimize data entry.

Really I think I should be using something like powerBI but excel is so amazing in it's multifacets that it always pulls me back.

Additionally, the SUMIFS & INDIRECT method REF#s if I don't have the referenced workbooks open. I know that both SUMIFS and INDIRECT are volatile functions but I haven't found any substitute for this method yet. Any ideas on how I should improve it, or if there is a different best practice for these types of comparisons?

Thanks!

 

[RoryA]

SUMIFS is not volatile (INDIRECT is).

Depending on how often you need to alter the source workbook(s), you might simply use the Edit links dialog instead of INDIRECT, which would at least get rid of the volatility. You could also use SUMPRODUCT rather than SUMIFS to allow the functions to work with closed workbooks. You could also use Power Query to pull the relevant data into your master workbook.