Workbook Control

[sjl109]

HI, I have multiple workbooks (Excel 2007) feeding into a master worksheet to create my company's financial models. I am trying to create a control of sorts that I could use on any of the sheets (each representing a department) to convert the values in various ranges to 0 so that I can model spinning off a division. I have been using IF statements for the cells containing data and basing it off of a cell at the top of the sheet that I have as being either 1 or 0 as the conditional in the function. My problem is that I know which cells I have modified and which need to be modified, but I cannot hand this off to an assistant to input budgetary numbers - so far the amount of IF functions and data entry has led to many typos... Does anyone know of a way that I could "turn a sheet on or off" using either a macro or combination of functions? Your help is very much appreciated.

 

[sjl109]

This is a shot of some of the data that I am attempting to manipulate. The upper right corner cell is the one that I am using as a control right now.

 

[konew1]

On your consolidation workbook bring each department data in onto a separate page. Keep the format of each page identical if possible, but if the deparments are very different then brin the data in at (say) row 20 and use the top 15 rows to extract the consolidation data and keep the top few rows identical. Put in a blank page before the first department, I will call it FIRST, and a blank sheet after the last department and call it LAST. On the summary page use the formula =sum(first:Last!A1) to sum up all the departments cell A1.
If you want to exclude a department drag that page outside the range of sheets bounded by FIRST and LAST and it will be excluded from your summary.
You may want to put workbook protection on so people dont move pages without realising that they are changing the result.