Hi,
The plumbing estimation software that we use spits out all of the information into an Excel document. The Detail tab is where every length of pipe, quantity of fitting, etc is organized within each spec that I used in my take off. My boss wants to have each quantity of pipe required rounded up to the nearest full length. I'm sick of manually calculating and entering, and there has to be a better way! I came up with a possible solution, but there one big annoying hurdle. In the Detail tab where the information is automatically populated, you cannot add any text or forumulas, because they are deleted everytime the file is exported (no matter how far over to the right you go). I created a copy of the detail tab and referenced each cell to the main detail tab, which works, but I would have to add a subtotal formula manually each time. Which would be annoying. Is there a formula to AutoSum each individual section (spec)? The size of each section and the number of sections will vary widely based on the requirements of the job. The Subtotal is at the bottom of each section, and outside of the first section there is a space between each section (as shown below).
For example, in the attached picture there are two Breaching (BRC) specs/sections from the main Detail tab. Below them are sixteen other specs/sections. (P.s. the pricing is fictional for this example)
Below is from the copied Detail Tab:
The plumbing estimation software that we use spits out all of the information into an Excel document. The Detail tab is where every length of pipe, quantity of fitting, etc is organized within each spec that I used in my take off. My boss wants to have each quantity of pipe required rounded up to the nearest full length. I'm sick of manually calculating and entering, and there has to be a better way! I came up with a possible solution, but there one big annoying hurdle. In the Detail tab where the information is automatically populated, you cannot add any text or forumulas, because they are deleted everytime the file is exported (no matter how far over to the right you go). I created a copy of the detail tab and referenced each cell to the main detail tab, which works, but I would have to add a subtotal formula manually each time. Which would be annoying. Is there a formula to AutoSum each individual section (spec)? The size of each section and the number of sections will vary widely based on the requirements of the job. The Subtotal is at the bottom of each section, and outside of the first section there is a space between each section (as shown below).
For example, in the attached picture there are two Breaching (BRC) specs/sections from the main Detail tab. Below them are sixteen other specs/sections. (P.s. the pricing is fictional for this example)
| DETAIL TAB TEST.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | G | H | I | J | |||||
| 8 | List | Matl | Unit | Price | ||||||||
| 9 | Spec | Qty | Size | Description | Price | Disc | Price | Total | ||||
| 10 | BRC01 | 103.81 | 8 | CPVC Sys 636 Pipe (10LZ) | $ 8.00 | Q | $ 8.00 | $ 830.48 | ||||
| 11 | 10 | 8 | Solvent Weld Joints | $ 2.00 | 1.0 | $ 2.00 | $ 20.00 | |||||
| 12 | 5 | 8 | Flue Gas 636 CPVC Coupling | $ 15.00 | Q | $ 15.00 | $ 75.00 | |||||
| 13 | 14 | 8 | Pipe Label | $ 8.00 | 1.0 | $ 8.00 | $ 112.00 | |||||
| 14 | 26 | 8 | Plain Standard Clevis Hanger | $ 20.00 | Q | $ 20.00 | $ 520.00 | |||||
| 15 | 78 | 1/2 | Hex Nuts | $ 0.25 | Q | $ 0.25 | $ 19.50 | |||||
| 16 | 78 | 1/2 | Plain Washer | $ 0.21 | Q | $ 0.21 | $ 16.38 | |||||
| 17 | 26 | 1/2 | Plain Wide Jaw Top C-Clamp | $ 4.00 | Q | $ 4.00 | $ 104.00 | |||||
| 18 | 104 | 1/2 | Plain Threaded Rod (10LZ) | $ 1.20 | Q | $ 1.20 | $ 124.80 | |||||
| 19 | BRC01 | 444.81 | (SUBTOTAL) | $ 1,822.16 | ||||||||
| 20 | ||||||||||||
| 21 | BRC02 | 69.31 | 4 | PVC Sys 636 Pipe (10LZ) | $ 15.00 | Q | $ 15.00 | $ 1,039.60 | ||||
| 22 | 1 | 4 | Pipe Label | $ 8.00 | 1.0 | $ 8.00 | $ 8.00 | |||||
| 23 | 13 | 4 | Flue Gas 636 PVC 90 Ell | $ 30.00 | Q | $ 30.00 | $ 390.00 | |||||
| 24 | 30 | 4 | Solvent Weld Joints | $ 2.00 | 1.0 | $ 2.00 | $ 60.00 | |||||
| 25 | 2 | 4 | Flue Gas 636 PVC Coupling | $ 12.00 | Q | $ 12.00 | $ 24.00 | |||||
| 26 | 9 | 4 | Pipe Label | $ 8.00 | 1.0 | $ 8.00 | $ 72.00 | |||||
| 27 | 15 | 4 | Plain Standard Clevis Hanger | $ 6.00 | Q | $ 6.00 | $ 90.00 | |||||
| 28 | 15 | 3/8 | Hex Nuts | $ 0.15 | Q | $ 0.15 | $ 2.25 | |||||
| 29 | 15 | 3/8 | Plain Washer | $ 0.10 | Q | $ 0.10 | $ 1.50 | |||||
| 30 | 15 | 3/8 | Plain Wide Jaw Top C-Clamp | $ 3.15 | Q | $ 3.15 | $ 47.25 | |||||
| 31 | 60 | 3/8 | Plain Threaded Rod (10LZ) | $ 0.80 | Q | $ 0.80 | $ 48.00 | |||||
| 32 | BRC02 | 244.31 | (SUBTOTAL) | $ 1,782.60 | ||||||||
Detail | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I10:I18,I21:I31 | I10 | =G10*IF(H10="Q",1,H10) |
| B19,J19 | B19 | =SUM(B10:B18) |
| J10:J18,J21:J31 | J10 | =B10*I10 |
| B32,J32 | B32 | =SUM(B21:B31) |
Below is from the copied Detail Tab:
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:E26 | B4 | =IF(Detail!A10="","",Detail!A10) |
| F4:G26 | F4 | =IF(Detail!I10="","",Detail!I10) |
