I am having an issue in a report where I have used Dynamic ranges. It is an Income Statement style report with sections for Revenue, Admin Expenses and Operational Expenses, with a sub total beneath each section.
I have multiple dynamic ranges defined based on the detail that needs to be shown. I have also set them up to remove zero rows. All works well except that once the dynamic ranges have been refreshed my sum ranges do not get updated for each of my subtotals (ie. SUM(F20:F26) does not expand to include new rows added and should change to SUM(F20:F32)). The only total that gets updated properly is the first one (Revenue section).
I have tried a number of things but no matter what I do I cannot get the Totals ranges to update. This workbook has over 20 worksheets. If the client needs to change the totals formulas every-time they may not be too happy.
Has anyone seen this before or have any thoughts on how to resolve this?
Have you tried formatting these ranges as tables? This would had the same effect as dynamic ranges as Excel will automatically adjust the range's size.
The SUM formulae then become static, eg.; =SUM(Table1[Sales])
You can use Name Manager to rename the tables to something more sensible than Table1, Table2 etc.