I created a spreadsheet with multiple tabs. Information from one spreadsheet feeds the others, goal is to limit input to as few locations and possible and still produce sorted information that can be shared with various parties that need different information. My formulas right now work, however, they are line for line, so if a condition isn't met I have blank lines in some of my spreadsheets. Any thoughts on how I can make this so that I don't have blank lines on the spreadsheets?
Data is initially input on the Potential PCO tab, which feeds the Master Tab.
This is the formulas in the Master Tab
Once certain criteria is met information from the Potential PCO tab moves to the Master Tab. Once certain criteria is met within the Master Tab, information then gets filled in on the Sub COR Log and PCO Log tabs. However, these two spreadsheets are keeping place holders until the criteria is met, which in some cases it might not ever. I like everything about how the data is feeding, except for the row placeholders and I can't seem to figure out how to change that.
Here are the formulas in Sub COR Log
Here is the formula in the PCO Log
Data is initially input on the Potential PCO tab, which feeds the Master Tab.
This is the formulas in the Master Tab
| 2023.01.24 PCO Log.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Master Log | |||||||||||||||
| 2 | Trade Partner | Date | PCO Title | Amount | Schedule Impact | Type | Status | Sub COR # | Complete Pricing Package | PCO # | Owner Directive | OCO # | Sub CO # | Notes | ||
| 3 | Baker Concrete | 1/24/2023 | Scratch Testing | OCO | Pending | |||||||||||
| 4 | Baker Concrete | 1/24/2023 | Additional Rock Excavation | OCO | Pending | GEOS Required per site visit | ||||||||||
| 5 | Baker Concrete | 1/24/2023 | Placing 2nd Boom starting at ground level | OCO | Pending | |||||||||||
| 6 | Baker Concrete | 1/24/2023 | Column D5.8 Foundation - Pilaster Foundation | Yes | OCO | Pending | GEOS Required based on rock condition, RFI 87 | |||||||||
| 7 | Baker Concrete | 1/24/2023 | Lean Fill Allowance - Open T&M | Allowance | Pending | Will exceed Owner Allowance, talk to Chris | ||||||||||
| 8 | Baker Concrete | 1/24/2023 | Additional Excavation @ Elevator Pit | Yes | OCO | Pending | GEOS Required per site visit, RFI 88 & 89 | |||||||||
| 9 | Baker Concrete | 1/24/2023 | Premium time to accelerate elevator core | Yes | OCO | 7 | Potentially required to maintain schedule due to RFI 88 & 89 | |||||||||
| 10 | Civil Constructors | 1/3/2023 | Additional Excavation and Fill @ direction of GEOS | Need backup from Civil & GEOS | ||||||||||||
| 11 | Cleary | 1/3/2023 | Additional Hydrant | $ 13,755.05 | No | OCO | Owner PCO | 1 | Yes | Proceed and Price | AHJ Requirement | |||||
| 12 | Cleary | 1/3/2023 | Replacing Five Lead Water Service Lines | $ 52,503.28 | No | OCO | Owner PCO | 2 | Yes | Proceed and Price | AHJ Requirement | |||||
| 13 | Cleary | 1/3/2023 | Upsizing NES Vault from 4" to 6" | OCO | Pending | 3 | NES Requirement | |||||||||
| 14 | Civil Constructors | 1/3/2023 | Other Trade Haul Off Exceeding the Allowance | $ 6,764.06 | No | OCO | Owner PCO | 1 | Yes | Proceed and Price | Need backup from Civil | |||||
| 15 | McDougal | 1/3/2023 | Garage Screening Selection | $ 103,821.00 | No | OCO | Owner PCO | 1 | Yes | 6 | Proceed and Price | 4 | Design Change, Owner Directive | |||
| 16 | Tate Fabrication | 12/22/2022 | Column Protection (Column Corner Guards/Wraps) | $ 21,749.36 | No | OCO | Approved | 1 | Yes | 3 | Proceed and Price | 3 | Owner Directive | |||
| 17 | 0 | 12/22/2022 | Deduct Unused Allowances | $ (120,974.00) | No | OCO | Approved | 2 | Proceed and Price | 2 | Accounting Cleanup | |||||
| 18 | 0 | 11/3/2022 | Adjustment to Project Schedule | $ - | Yes | OCO | Approved | 1 | Proceed and Price | 1 | Start date moved from 8/5/2022 to 8/18/2022, new completion date 11/11/2024 | |||||
Master | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A3:A18 | A3 | =IF('Potential PCOs'!F3="Yes",'Potential PCOs'!A3,"") |
| B3:B18 | B3 | =IF('Potential PCOs'!F3="Yes",'Potential PCOs'!B3,"") |
| C3:C18 | C3 | =IF('Potential PCOs'!F3="Yes",'Potential PCOs'!C3,"") |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| G2 | Any value | |
| E3:E101 | List | Yes, No |
| F3:F100 | List | Internal, Contingency, Allowance, OCO |
| G3:G101 | List | Pending, Under Review, Owner PCO, Owner Review, Approved, Rejected, Void |
| H2:H125 | Any value | |
| I2 | Any value | |
| I3:I101 | List | Yes, No |
| K3:K18 | List | Price, Proceed and Price |
Once certain criteria is met information from the Potential PCO tab moves to the Master Tab. Once certain criteria is met within the Master Tab, information then gets filled in on the Sub COR Log and PCO Log tabs. However, these two spreadsheets are keeping place holders until the criteria is met, which in some cases it might not ever. I like everything about how the data is feeding, except for the row placeholders and I can't seem to figure out how to change that.
Here are the formulas in Sub COR Log
| 2023.01.24 PCO Log.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Sub COR Log | ||||||||||||
| 2 | Trade Partner | Date | PCO Title | Amount | Schedule Impact | Type | Status | Sub COR # | Complete Pricing Package | Sub CO # | Notes | ||
| 3 | |||||||||||||
| 4 | |||||||||||||
| 5 | |||||||||||||
| 6 | |||||||||||||
| 7 | |||||||||||||
| 8 | |||||||||||||
| 9 | |||||||||||||
| 10 | |||||||||||||
| 11 | Cleary | 1/3/2023 | Additional Hydrant | $13,755.05 | No | OCO | Owner PCO | 1 | Yes | 0 | AHJ Requirement | ||
| 12 | Cleary | 1/3/2023 | Replacing Five Lead Water Service Lines | $52,503.28 | No | OCO | Owner PCO | 2 | Yes | 0 | AHJ Requirement | ||
| 13 | |||||||||||||
| 14 | Civil Constructors | 1/3/2023 | Other Trade Haul Off Exceeding the Allowance | $6,764.06 | No | OCO | Owner PCO | 1 | Yes | 0 | Need backup from Civil | ||
| 15 | McDougal | 1/3/2023 | Garage Screening Selection | $103,821.00 | No | OCO | Owner PCO | 1 | Yes | 0 | Design Change, Owner Directive | ||
| 16 | Tate Fabrication | 12/22/2022 | Column Protection (Column Corner Guards/Wraps) | $21,749.36 | No | OCO | Approved | 1 | Yes | 0 | Owner Directive | ||
Sub COR Log | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A3:A16 | A3 | =IF(Master!I3="Yes",Master!A3,"") |
| B3:B16 | B3 | =IF(Master!I3="Yes",Master!B3,"") |
| C3:C16 | C3 | =IF(Master!I3="Yes",Master!C3,"") |
| D3:D16 | D3 | =IF(Master!I3="Yes",Master!D3,"") |
| E3:E16 | E3 | =IF(Master!I3="Yes",Master!E3,"") |
| F3:F16 | F3 | =IF(Master!I3="Yes",Master!F3,"") |
| G3:G16 | G3 | =IF(Master!I3="Yes",Master!G3,"") |
| H3:H16 | H3 | =IF(Master!I3="Yes",Master!H3,"") |
| I3:I16 | I3 | =IF(Master!I3="Yes",Master!I3,"") |
| J3:J16 | J3 | =IF(Master!I3="Yes",Master!M3,"") |
| K3:K16 | K3 | =IF(Master!I3="Yes",Master!N3,"") |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| G2:I2 | Any value | |
Here is the formula in the PCO Log
| 2023.01.24 PCO Log.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | PCO Log | |||||||||||
| 2 | Date | PCO Title | Amount | Schedule Impact | Type | Status | PCO # | Owner Directive | OCO # | Notes | ||
| 3 | ||||||||||||
| 4 | ||||||||||||
| 5 | ||||||||||||
| 6 | ||||||||||||
| 7 | ||||||||||||
| 8 | ||||||||||||
| 9 | ||||||||||||
| 10 | ||||||||||||
| 11 | 1/3/2023 | Additional Hydrant | No | OCO | Owner PCO | 0 | Proceed and Price | 0 | AHJ Requirement | |||
| 12 | 1/3/2023 | Replacing Five Lead Water Service Lines | No | OCO | Owner PCO | 0 | Proceed and Price | 0 | AHJ Requirement | |||
| 13 | ||||||||||||
| 14 | 1/3/2023 | Other Trade Haul Off Exceeding the Allowance | No | OCO | Owner PCO | 0 | Proceed and Price | 0 | Need backup from Civil | |||
| 15 | 1/3/2023 | Garage Screening Selection | No | OCO | Owner PCO | 6 | Proceed and Price | 4 | Design Change, Owner Directive | |||
| 16 | 12/22/2022 | Column Protection (Column Corner Guards/Wraps) | $ 22,662.81 | No | OCO | Approved | 3 | Proceed and Price | 3 | Owner Directive | ||
| 17 | 12/22/2022 | Deduct Unused Allowances | $ (120,974.00) | No | OCO | Approved | 2 | Proceed and Price | 2 | Accounting Cleanup | ||
| 18 | 11/3/2022 | Adjustment to Project Schedule | $ - | Yes | OCO | Approved | 1 | Proceed and Price | 1 | Start date moved from 8/5/2022 to 8/18/2022, new completion date 11/11/2024 | ||
PCO Log | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A3 | A3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}),Master!B3," ") |
| B3:B18 | B3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}), Master!C3," ") |
| A4:A18 | A4 | =IF(OR(Master!G4={"Owner PCO","Owner Review","Approved","Rejected"}), Master!B4," ") |
| E3:E18 | E3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}), Master!F3," ") |
| F3:F18 | F3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}), Master!G3," ") |
| G3:G18 | G3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}), Master!J3," ") |
| H3:H18 | H3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}), Master!K3," ") |
| I3:I18 | I3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}), Master!L3," ") |
| J3:J18 | J3 | =IF(OR(Master!G3={"Owner PCO","Owner Review","Approved","Rejected"}), Master!N3," ") |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D3:D18 | List | Yes, No |
| F2 | Any value | |
