TLDR
Linking design assumptions to drawings in Excel requires a relational data structure rather than a simple list. By using unique identifiers (UIDs) for both assumptions and drawings and connecting them via a mapping table or a dedicated "Affected Drawings" column, engineers can track which design decisions impact specific deliverables and identify the ripple effect of changes.

The challenge of linking assumptions to drawings

Linking design assumptions to drawings involves creating a traceable path between a theoretical decision and its physical representation. In Excel, this is best achieved by assigning a Unique ID to every assumption, recording it, then using a relational table to map the ID of an assumption to the drawing's number, ensuring that any change to an assumption triggers a review of all linked drawings.

In our experience, the primary failure in manual tracking is the "silo effect." An engineer records an assumption about soil bearing capacity in a separate log, for example, but the structural drawings are produced without a direct reference to that log entry. When the geotechnical report is finally updated and the assumption is proven wrong:

Firstly, would they even connect an incoming geotech report to remembering that the wrong SBC was used in the design?

Secondly, the team then spends hours manually hunting through the drawing set to find every instance where that specific value was used.

This lack of traceability is a leading cause of late-stage design revisions and avoidable profitability leaks; indeed, ResearchGate notes that design errors can significantly impact overall project costs, delay completion, and decrease project quality.

Creating a relational structure in Excel

To link assumptions to drawings effectively, you must move away from a single flat sheet and toward a relational database mindset. This means separating your data into three distinct components: an Assumptions Register, a Drawing Register, and a packages (through a Work Breakdown Structure). This structure prevents data duplication and allows for multi-directional tracking.

Now, think of the following cases:

You're about to issue a number of drawings, but by clicking on a single button for the assumptions used on a drawing, you're able to see all of the assumptions used on that package that could relate to that drawing including, for example, a live load assumed for the equipment room that's since been increased. Without seeing that relation between the drawing and that assumption, on large projects with so much going on, you might forget that your team hasn't resolved that assumption and updated the slab thickness or rebar in that area as a result of that change in live load.

Or, think of the example where the geotech report does come in for one of the areas on your large project which resolves an assumption that was originally made. By having tagged drawings and reports to a package (usually delineated by area of construction), you're able to know exactly which report and drawings need to be updated as a result of the incoming information.

The Assumptions Register

The Assumptions Register should serve as the single source of truth for every design decision based on incomplete information. Each entry must have a Unique ID (e.g., ASSUMP-001), a clear description of the assumption, the date it was made, the person responsible, and a status (Open, Validated, or Superseded). Because assumptions are an inherent part of the design process, they must be stated explicitly and justified.

Our data suggests that adding a "Risk Level" column to this register is critical. By either grading assumptions as Low, Medium, or High risk, or indeed, by automatically mapping each assumption to a design risk in the Design Risk Register, engineers can prioritize which assumptions need validation first. A high-risk assumption linked to ten different drawings represents a significant project exposure. When the assumption is updated, the UID allows you to filter your entire workbook to see exactly where the impact lands, reducing the risk of missing a critical update in a complex package.

The Drawing Register

The Drawing Register tracks the status and version of every deliverable. Each drawing must have a unique Drawing Number, a title, a current revision, and its designated package. This register acts as the anchor point for all other project data.

For the linking to work, the Drawing Register must be kept current. Without a rigorous process, firms risk the "version chaos" described in Drawing Revision Control, where working from outdated revisions leads to expensive rework and wasted materials. We recommend using Data Validation (drop-down menus) in Excel to ensure that when a user links an assumption to a drawing or package, they are selecting from a predefined list of valid drawing numbers rather than typing them manually, which prevents typos from breaking the traceability.

Establishing the link

The most robust way to link these two registers is through a "Linking Table" or a many-to-many mapping sheet. In this sheet, you simply list the Assumption ID in one column and the Drawing ID in the next. This allows one assumption to be linked to multiple drawings, and one drawing to be influenced by multiple assumptions.

For those who prefer a simpler approach, you can add an "Affected Drawings" column to the Assumptions Register. However, this requires using comma-separated values (e.g., DWG-101, DWG-102), which makes filtering and reporting difficult. Using Power Query in Excel is the professional standard here. By applying many-to-many relationship management techniques as outlined by SQLBI, you can merge the Assumptions and Drawing tables based on their UIDs, generating a real-time report of all "at-risk" drawings whenever an assumption status changes to "Superseded."

Risks of manual Excel tracking

Manual tracking in Excel is prone to human error, version conflict, and "data rot," where the register becomes outdated because the effort to maintain it exceeds the perceived immediate value. As a project scales, the time spent updating these links often consumes the time that should be spent on actual engineering.

We have observed that in firms relying solely on Excel, the "linkage" often exists only in the lead engineer's head. When that individual is unavailable, or worse, resigns, the team lacks the visibility to know which drawings are dependent on which assumptions. Furthermore, Excel lacks a native audit trail; if a link is deleted or an assumption is changed without a record of why, the project loses its defensive documentation. This creates significant liability during the construction phase. Professional liability claims often arise from alleged mistakes, negligence, or omissions in design, and a lack of documented logic can lead to costly and reputationally damaging claims.

Moving toward automated design registers

To eliminate the fragility of Excel, firms are shifting toward unified register systems that automate the linking process. These systems treat assumptions, RFIs, and drawings as interconnected objects rather than static rows in a spreadsheet, ensuring that a change in one area automatically flags the affected deliverables for review.

Construct Digitally SmartRegister solves this by automatically linking design risks and assumptions to affected drawings and packages. Instead of manually updating mapping tables, the system surfaces resolution deadlines and highlights profitability exposures for directors. This transition moves the firm from a reactive state - searching for errors after they happen - to a proactive state where the impact of every design change is visible in real-time across the entire project portfolio.

Key Takeaways

Relational Mapping: Use Unique IDs (UIDs) for both assumptions and drawings to create a traceable link.
Structure: Maintain separate registers for assumptions and drawings, connected by a linking table or Power Query.
Risk Grading: Assign risk levels to assumptions to prioritize validation efforts.
Validation: Use Excel's Data Validation to prevent typos from breaking links between registers.
Automation: Transition to dedicated tools to avoid the version control and audit trail failures inherent in manual spreadsheets.

To move beyond manual trackers and reduce your design risk, explore how Construct Digitally SmartRegister can automate your project registers.

Loading...