Dynamic & Scalable Model

webehighrollin

New Member
Joined
Jan 5, 2012
Messages
3
Hello - Thanks for your time. I have a question on what formulas/ideas I could use to achieve the following.

I'm working on a Forecast model that has multiple tabs. These tabs represent the different functions in my company (IT, Risk, Marketing, etc). On each of these tabs I have identical tables with different expense accounts and my forecasted expenses by year (i.e. Salaries, Bonus, Furniture going down on the columns, and 2017, 2018, 2019, 2020 on rows going across).

On a separate summary tab, I'd like to build a table with dynamic formulas that allows me to see a consolidated view of all the expenses across the different functions. I'd like for the formulas to be dynamic in the sense that if I were to add additional tabs in the future, this summary tab could quickly include the new functions, rather than me having to edit all the formulas and manually add in the new tabs.

Is indirect the best function to use?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. Windows
Its fairly simple to do if you know the column the year is going to be on:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetNames&"'!B:B"),INDIRECT("'"&SheetNames&"'!A:A"),"<>"))

where SheetNames is a named range containing the sheet names.

Im thinking its possible to alter the column based on year but i cant think how at the mo.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,952
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top