ChrisMas92
New Member
- Joined
- Nov 27, 2023
- Messages
- 1
- Office Version
- 2016
- Platform
- Windows
Hello,
I am trying to develop a smart calculation sheet as a passion project and wanted to see if what I have in my head is possible to do in excel. I am very new to VBA.
Background:
I am a highway engineer and we have several worksheets for drainage calculations and they often include duplicate information. I wanted to try and streamline the process for ease of use and to mitigate potential of a mistake (changing a number in one place, but missing it somewhere else).
The first sheet is where you plug in the "physical data" for an inlet (the thing often on the side of the road that capture water on the road; storm drain, catch basin, whatever you may know it as). Yellow cells are basic data entry cells, blue are data validation drop downs, and white are numbers calculated automatically based on inputs. The Add Inlet button creates a new row with all of the formulae in tact so data can be entered for the next inlet. The sheet looks like this:
The second sheet is a template calculation sheet that we usually copy manually for each inlet. This sheet needs to be filled out for each inlet and preferably linked to information from the Inlet Data sheet, such as ID Number from A11 to the calc sheet A1. The some numbers from this sheet are used in calculations for the next sheet. A portion of the calc sheet template looks like this:
The third sheet takes information from the previous two sheets and calculates the gutter flow (basically the anticipated amount of flooding the road is going to have in the event of heavy rain...not important). The number of rows and inlets should match the Inlet Data sheet. Basically these calculations need to be run for each row using data from its corresponding row in the Inlet Data sheet and from the inlet's specific calc sheet. For example the formula in D13 needs to pull numbers from Inlet Data cells F11, G11, and H11 and also from Calc Sheet cell C42. This sheet is not fully set up because I was not sure how I would be proceeding.
My Hope:
I would love to have the ability to create a row on the Inlet Data sheet, fill out the information in the row and then double click the cell in Column K and have a few things happen:
1. Create a new sheet from the Calc Sheet Template specific to that row that is named based off of the info in Column A
2. Reference cells from that specific row to the calc sheet:
3. Generate a row on the Gutter Flow sheet that takes data from the Inlet Data row in question and from the newly generated Calc sheet. Some are direct references:
Some are in formulas. For example the formula in Gutter Flow cell D13 needs to read =(Calc!$C$42/((0.56/InletData[Manning''s n])*(InletData[Cross Slope of Shoulder (ft/ft)]^1.67)*(InletData[Grade of Gutter (ft/ft)]^0.5)))^0.3745, pulling info from the table on the Inlet Data sheet and the calc sheet.
At the end of the day, the workbook would have the Inlet Data tab with X inlet rows, X number of calc sheets, and a Gutter Flow tab with X number of rows. I feel like I am asking Excel to do too much at once, but maybe there is some solution here that I am no experienced enough to know. The link to the file is below. Thank you!
Gutter Flow Excel Workbook
I am trying to develop a smart calculation sheet as a passion project and wanted to see if what I have in my head is possible to do in excel. I am very new to VBA.
Background:
I am a highway engineer and we have several worksheets for drainage calculations and they often include duplicate information. I wanted to try and streamline the process for ease of use and to mitigate potential of a mistake (changing a number in one place, but missing it somewhere else).
The first sheet is where you plug in the "physical data" for an inlet (the thing often on the side of the road that capture water on the road; storm drain, catch basin, whatever you may know it as). Yellow cells are basic data entry cells, blue are data validation drop downs, and white are numbers calculated automatically based on inputs. The Add Inlet button creates a new row with all of the formulae in tact so data can be entered for the next inlet. The sheet looks like this:
The second sheet is a template calculation sheet that we usually copy manually for each inlet. This sheet needs to be filled out for each inlet and preferably linked to information from the Inlet Data sheet, such as ID Number from A11 to the calc sheet A1. The some numbers from this sheet are used in calculations for the next sheet. A portion of the calc sheet template looks like this:
The third sheet takes information from the previous two sheets and calculates the gutter flow (basically the anticipated amount of flooding the road is going to have in the event of heavy rain...not important). The number of rows and inlets should match the Inlet Data sheet. Basically these calculations need to be run for each row using data from its corresponding row in the Inlet Data sheet and from the inlet's specific calc sheet. For example the formula in D13 needs to pull numbers from Inlet Data cells F11, G11, and H11 and also from Calc Sheet cell C42. This sheet is not fully set up because I was not sure how I would be proceeding.
My Hope:
I would love to have the ability to create a row on the Inlet Data sheet, fill out the information in the row and then double click the cell in Column K and have a few things happen:
1. Create a new sheet from the Calc Sheet Template specific to that row that is named based off of the info in Column A
2. Reference cells from that specific row to the calc sheet:
| Inlet Data | Column A | Column B | Column C |
| Calc Sheet | A1 | A2 | B2 |
| Inlet Data | Column A | Column B | Column C |
| Gutter Flow | Column A | Column C | Column C |
At the end of the day, the workbook would have the Inlet Data tab with X inlet rows, X number of calc sheets, and a Gutter Flow tab with X number of rows. I feel like I am asking Excel to do too much at once, but maybe there is some solution here that I am no experienced enough to know. The link to the file is below. Thank you!
Gutter Flow Excel Workbook
