Project Management View Log
Creating an advanced Project Management View (PMV) Excel log to update construction document control information requires several steps. Below is a step-by-step guide to help you develop a professional and efficient document control system. The log will include features like tracking days elapsed, color formatting, and dropdown lists for document types, discipline, status, etc.
Step 1: Define the Structure of Your Excel Log
Before starting with formulas and formatting, it's important to lay out the structure of your log. Here is a basic layout suggestion for columns:
Document Number – Unique number for each document.
Document Title – Title or description of the document.
Document Type – Dropdown list for document type (e.g., Drawing, Report, Specification, etc.).
Discipline – Dropdown list for project discipline (e.g., Civil, Mechanical, Electrical, etc.).
Status – Dropdown list for the status of the document (e.g., Submitted, Approved, Rejected, In Review, etc.).
Date Issued – The date the document was issued.
Date Received – The date the document was received (optional).
Days Elapsed – Automatically calculated as the number of days since the document was issued.
Comments – Any additional notes about the document.
Step 2: Set Up Dropdown Lists
To create dropdown lists for columns like Document Type, Discipline, and Status, follow these steps:
Go to a new sheet (e.g., "Dropdown Data") to define your lists.
In column A, list your Document Types (e.g., Drawing, Report, etc.).
In column B, list your Disciplines (e.g., Civil, Mechanical, etc.).
In column C, list the Statuses (e.g., Submitted, Approved, etc.).
Select the cells where the dropdown should appear (e.g., Column C for Document Type).
Go to the Data tab → Data Validation → Data Validation.
Choose List from the Allow dropdown.
In the Source field, select the list you created on the "Dropdown Data" sheet.
Repeat for each column with a dropdown list.
Step 3: Set Up Formulas for Calculating Days Elapsed
To calculate the Days Elapsed:
In the Days Elapsed column, enter the following formula:
excel
Copy code
=IF(ISBLANK(G2), "", TODAY() - G2)
This assumes G2 is the cell containing the Date Issued. The formula calculates the difference between the current date (TODAY()) and the Date Issued.
Copy this formula down the column for all rows where data is entered.
Step 4: Apply Conditional Formatting for Color Coding
Conditional formatting helps visually track the document status or urgency based on elapsed days or other criteria. Here’s how to apply it:
Select the cells in the Days Elapsed column.
Go to the Home tab → Conditional Formatting → New Rule.
Choose Format cells based on their values.
Set the rule according to the number of days elapsed. For example:
Less than 10 days: Set the formatting to green (indicating it’s recent).
10 to 30 days: Set the formatting to yellow (indicating attention needed soon).
More than 30 days: Set the formatting to red (indicating overdue).
Similarly, apply conditional formatting to the Status column:
Approved → Green.
Rejected → Red.
In Review → Yellow.
Step 5: Lock Cells for Certain Columns
To ensure that only specific cells can be edited (like dropdowns and comments), lock the rest of the sheet. Here’s how:
Select all the cells that should remain editable (e.g., Document Type, Discipline, Status, and Comments).
Right-click and choose Format Cells → Protection → Uncheck the Locked box.
Go to the Review tab → Protect Sheet, and enable protection while allowing editing in the cells you just unlocked.
Step 6: Add Filters to Your Log
Filters help to easily navigate and manage large amounts of data. To apply filters:
Select the top row containing your column headers.
Go to the Data tab → Filter.
This will allow you to filter based on Document Type, Discipline, Status, and other fields.
Step 7: Final Formatting Touches
Freeze Panes: To keep headers visible when scrolling, go to View → Freeze Panes → Freeze Top Row.
Column Width: Adjust the column widths so that all data is easily visible without requiring too much scrolling.
Gridlines: You may choose to hide gridlines for a cleaner look by going to the View tab and unchecking Gridlines.
Step 8: Test and Review
After setting everything up:
Test the dropdown menus, data validation, and conditional formatting.
Verify that the Days Elapsed calculation works as intended.
Adjust color formatting rules or dropdowns if needed.
Step 9: Save and Share
Once everything is working perfectly, save your Excel file. If this log will be shared with others, you can protect certain cells or the entire workbook to prevent accidental edits to formulas or important data.
Sample Layout for the Log:
Document Number Document Title Document Type Discipline Status Date Issued Date Received Days Elapsed Comments
DOC-001 Site Plan Drawing Civil Approved 01-Jan-2024 05-Jan-2024 90 N/A
DOC-002 Electrical Plan Report Electrical In Review 05-Jan-2024 10-Jan-2024 85 Pending approval
This process should provide you with a comprehensive document control system in Excel that includes days tracking, color-coded urgency, and dropdowns for easy data entry.