Creating a custom solution for a better Excel workflow
Nate Martinez Industrial Services (NMIS), an industrial construction services company out of Denver, recently reviewed their workflows and found some deficiencies in the ways in which they were using Microsoft Excel to create their submittal forms. Digital Workshop Center stepped in to create a custom Excel and Visual Basic Application (VBA) to help streamline their processes and help save them tremendous amounts of time.
The goal of this project was to create an automated solution to create cover sheets and submittal registers, which had been entered manually in Excel in the past. After reviewing the current processes with NMIS, DWC determined that they did not have an effective system currently in place to create these documents. There was a tremendous amount of wasted time from the manual design of each form. Different users created the forms in different ways, and there was a lack of consistency of the final product.
DWC staff interviewed key figures to learn the best practices for NMIS and what processes were needed. After the interview phase, certain processes were identified that could be automated with some custom code. A basic design was provided by NMIS, and we used that as a first draft.
The workflow DWC designed for NMIS was as follows:
- The user entered the Specification names into the first worksheet
- The user checked off the systems selected for this project
- On click of update, the systems were moved to the next worksheet automatically. The user would now choose the appropriate Specifications and add them to the subsequent worksheets.
- On click of update, Cover Sheets would be auto-generated and relevant data copied over. Some manual data would be entered at this point.
- After all of the data is entered into the Cover Sheets, the final Submittal Register would be updated and compile all of the data from the other sheets onto this one master record.
- For both the Cover Sheets and Submittal Register, the Export PDF option was provided, as well as user improvements such as double-click functions to add new rows, ability to update data easily, and quick navigation to other worksheets.
One of the main challenges we had to design for the users was to track Specification Sections across the project. Each Specification Section must be used at least once across the project, so we provided a custom VBA form for the users to track which Specifications have already been assigned and those that are yet to be assigned. Some additional functionality was added to improve the user experience and make this part of the process as user-friendly as possible.
Project Review – NM Industrial Service – Summary
The final result of this project delivered a brand new system for NMIS and create an automated workflow that had not existed in the past. Additional features were added to track parts more efficiently and create a workflow to assign default items to a system for the future. The client will save an incredible amount of time and productivity by automating with Excel and some custom VBA, and DWC had a great experience creating these systems for NMIS.
Need a custom Excel or VBA project?
Let’s talk! We have designed VBA Excel projects for companies of all sizes across the United States. Fill out the following form to schedule a free consultation.