Dear all, I am trying to consolidate cashflows for over 30 projects into a single consol spreadsheet. As the universe of projects is ever-changing, I would like to be able to quickly Inclue/Exclude them from the consol spreadsheet. Besides the “Consolidate Data” tool, I heard about an alternative method for quickly doing this… it involves creating 2 “bracket tabs”… to include projects just drag and drop the project tabs within the “bracket tabs”… conversely to exclude just drag the project tabs out of the “bracket tabs”… However, I’m not quite sure how to implement this. Does anyone recall the name of this function? If you can give me some keywords I’ll go google it myself… right now the search results are all related to “consolidate data” tool… Thanks, cfaprincess
I think I know what you are talking about, if so here is what you do: Make tabs called “Begin” and “End” and place them at the beginning and end of the sheets you want to consolidate. Then use the following formula on the consolidation tab to sum the tabs between “Begin” and “End”: =sum(‘Begin:End’!A1) -> A1 being whatever cell you are trying to sum across all the worksheets. Hope this helps.
Thanks KrukVT! This is exactly what I was referring to. If I’m not wrong it’s called the 3D cell reference??
Very cool. Thanks KrukVT.
The “bracket tabs” concept is cool, we do things similar all the time and I’m sure I’m gonna borrow that idea! I also use drop down boxes when building models management will use, makes it very slick and easy for them to play around (and not hurt themselves). They just click in the drop down boxes and select which of the 30 projects they want to include…all the math is linked to their selections. In this case you would just name each tab the name of the project. Then on the consolidated tab you would have drop down boxes using a validation list (which would be a list of the 30 project tabs). Then a simple formula that brings in the cash flow of the projects they select.
clever. I’ve used 3d references before - but this is a really smart use of them.
I usually just use drop-downs with v-lookup functions