This post is part 1 of a series of posts I intend to pen related to a personal engineering tracking workbook that I put together for each of my projects. I plan to keep the length short and manageable, as well as breaking it out for others to go to those parts that they may be of interest.
Project Engineering Tracking Workbook
We will begin with a blank workbook and create 2 worksheets. One called Actions to input all the actions on the project. The other worksheet called Lookup, where some information will be extracted from.
This workbook will be the basis for project tracking which you can extend to suit your specific needs.
Let’s get started with the setup of the Action sheet, then we will move onto the Lookup sheet. Lastly, we will return to the Action sheet to complete the linkage between the two.
Tracking Workbook | The Action Sheet
The action sheet is the starting block for my professional tracking. I have tried various ways, systems, journals etc. Since I spend the bulk of my engineering career in a spreadsheet, I thought there must be a way to utilise this tool. So after a few iterations over several projects, with varying processes any systems, I have come up with the following spreadsheet to track all aspects related to my scope.
After many years of tracking against vague titles or document types, I concluded that there must be another way. As engineers, we generally produce deliverables which are assigned a document number. This document number is now what I use to track and associate with related project deliverable or requirements. The document number is what the database world classifies as the Primary Key.
Let’s start with the first table in the workbook, known as an Actions table. This table is where actions are logged for deliverable production or review. The table consists of input, lookup and calculated columns. The primary input is the document number. From the document number, we can pick up other information from other sources like a document management system or a master’s deliverable list. Extracting the information eliminates tedious manual entry as well as keeps it updated automatically. The calculated columns can be used for slicers or filters.
Setup the Action Table
Start with some column headings which suit your fancy. I have chosen the following: Status, Document Number, Remarks, Title, Rev, Issued, Area, Disc, DocType, SeqNo
Tip: Copy these headings (without spaces) Status,Document Number,Remarks,Title,Rev,Issued,Area,Disc,DocType, SeqNo and paste in Excel > Data > Text to Columns > Delimeter (Common) > Ok a couple of time.
Convert to a table (Ctrl+T) and rename the table as tbl_Actions.
Refer to this post more details on tables.
I generally try to keep the input, lookup and calculated columns grouped together and use some fill colour to distinguish the difference between them. This is entirely a personal preference.
Some commentary on the input columns:
- Status – I use a data validation list for this column and adjust the inputs in an input table on a separate input sheet to suit.
- Document Number – This will be the reference for the entire project. The document number needs to be unique and only appear once. I use a conditional format on this column to check for duplicates.
- Remarks – This is my history column. I use 1 cell for 1 document number as 1 document number can only occur once in the table as noted above. You need to be creative and use your twitter (limited number of characters) experiences. I date each remark with dd/mm to limit characters. I use an autotext expander to input the date in this format to limit the number of keystroke.
We will return to the lookup (reference) columns later after we have put together a table for which we can actually reference.
The calculated columns (Title, Rev, Issued, Area, Disc, DocType, SeqNo) are mainly for slicing and dicing the table.
Tracking Workbook | The Lookup Sheet
The lookup sheet contains the latest information associated with the document number. You should retrieve this information from another source generated by someone else. Why bother retyping this when someone else has already done the work!
Will set up here for illustrative purposes, but remember, make sure you get this data elsewhere.
Setup the Lookup Table
Let’s use the following columns in a new sheet: Doc No, Doc Title, Doc Rev, Doc Issued. Set it as a table, and rename it tbl_Lookup.
If you use the copy and paste tip above, remember to remove the space after the comma.
Now that the Lookup Table is complete, we will head back to the Action Table to enter some equations for data lookups and calculated columns.
Return to the Action Sheet
Populate the Lookup Columns
I prefer the INDEX/MATCH method for this, as covered on various of other posts here. To save you the spiel, I’ve just include the equations which can be copied and pasted directly into the actions table.
// For the Title =IFERROR( INDEX( tbl_Lookup[[Doc Title]], MATCH( [[Document Number]], tbl_Lookup[Doc No], 0 ) ), "") // For the Rev =IFERROR( INDEX( tbl_Lookup[Doc Rev], MATCH( [Document Number], tbl_Lookup[Doc No], 0 ) ), "") // For the Issued =IFERROR( INDEX( tbl_Lookup[Doc Issued], MATCH( [Document Number], tbl_Lookup[Doc No], 0 ) ), "")
Populate the Calculated Columns
Lastly, I like to split out the document number into its parts for use as Table slicers. The filters may already be part of your source file from the document management system, or you can use the following equations as a start.
// Area
=MID([@[Document Number]],3,2)
// Disc
=MID([@[Document Number]],6,2)
// DocType
=MID([@[Document Number]],9,3)
// SeqNo
=TRIM(RIGHT(SUBSTITUTE([@[Document Number]],"-",REPT(" ",100)),100))
The MID function shown is simple and may not be the best choice for all instances but it is a good starting place. I got the SeqNo equation from one of my favourite sites Exceljet.
Remarks
That’s the basis for my personal engineering tracking register. This is what I use to track all history related to a deliverable. I then expand on this worksheet to tie into WBS codes for time sheeting; equipment, line and valve lists; progress charting to name a few. I will explain some of these in other parts of this series.
I am always looking for a better or more efficient way of working, therefore, please drop a comment below if you have any suggestions or improvements for all to use!