I am working to make an Excel document that estimates the cost of a project more dynamic and adjustable as the project changes.  To make it simple, let's say the project is building a swimming pool.  I have 4 'Details' sheets:

'1.1  Foundation Details'

'1.2  Water System Details'

'1.3  Diving Board Details'

'1.4  Slide Details'

Each of these sheets has the estimated costs for that particular portion of the project divided out into all the different employees needed and equipment/supplies needed.  Also, there is a worksheet named 'Profile' that rolls up the totals of all four of these estimates and gives a total cost for the whole project. The last worksheet is full of all kinds of charts displaying the cost over the span of X years for various expenses. 

What I am trying to do is find a way to make this document easier to alter.  For example, if we chose to add '1.5 Baby Pool', we would have to redo a lot of formulas in the charts and also the 'Profile' sheet.  So, what I am wondering is if there is a way for make a separate sheet that formats the entire document.  For example, the sheet may have ask:

Number of 'Details':  _____

Number of Years to Complete:  ______

etc.

and then depending on what is filled in the document will create that many sheets.  Is this at all doable?? It just seems very inefficient to manually go through and change the whole document every time the project changes.  This document is used over and over again for all different kinds of projects and the number of 'Details' is always different - causing a lot of tedious work for me. 

THANKS!

Ideally, what you would want to do is use one sheet of data, with at least one extra column into which one of these values is entered:

Foundation

Water System

Diving Board

Slide

Then, when you decide to add Baby Pool, you just start entering that data into the same table, but with Baby Pool as the entry in your ID column.

Then you can use data filters and SubTotal functions or Pivot Tables or Pivot Charts - all powerful built-in tools that make data analysis easy: how many plumber-hours did you use in January of 2014, on the Water System? How about on the Baby Pool.  Boom, you're done.  You can summarize the data any way you want - labor, employees, equipment, supplies, etc. for any or all of the sub-parts. With that type of data structure, there is never an issue with finding the data or including or excluding points since the table is correctly structured to allow that to happen using built-in Excel features instead of formulas that are often sources of error.

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor 11 hours 30 minutes ago

Ideally, what you would want to do is use one sheet of data, with at least one extra column into which one of these values is entered:

Foundation

Water System

Diving Board

Slide

Then, when you decide to add Baby Pool, you just start entering that data into the same table, but with Baby Pool as the entry in your ID column.

Then you can use data filters and SubTotal functions or Pivot Tables or Pivot Charts - all powerful built-in tools that make data analysis easy: how many plumber-hours did you use in January of 2014, on the Water System? How about on the Baby Pool.  Boom, you're done.  You can summarize the data any way you want - labor, employees, equipment, supplies, etc. for any or all of the sub-parts. With that type of data structure, there is never an issue with finding the data or including or excluding points since the table is correctly structured to allow that to happen using built-in Excel features instead of formulas that are often sources of error.

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Friday, July 17, 2015 8:01 PM