Managing Documents with Spreadsheet.com

Whether you’re working on a short-term project with a small team or managing an entire project pipeline with dozens of stakeholders, document management is an important part of any project management workflow. Document management is the process of collecting, storing, and tracking documents like contracts and certifications.

In this guide, we’ll take a look at a document management workbook built for a construction project and explain how key Spreadsheet.com features like Related rows, Form views, and data types can be combined to create an all-in-one document management solution.

Creating Relationships between Worksheets with Related Rows

Related rows, one of Spreadsheet.com’s data types, let you link rows in multiple worksheets so that your sheets can work like tables in a relational database where editing one changes the data in another.

For our document management workbook, Related rows connect our Subcontractors and Documents worksheets so that the two can share information.

Use Related rows to Link Documents with Other Records

Our document management workbook has two sheets – a list of Subcontractors and a list of Documents. Each document originates with one of our subcontractors. Although this information is held in different worksheets, the records are linked with Related rows.

Column D - Documents Submitted in our Subcontractors worksheet and Column A - Subcontractor in our Documents worksheet are connected by a Related row with a 2-way link. With a 2-way link, the relationship between records is reflected in both sheets; the Subcontractors worksheet shows each subcontractor’s submitted documents, and the Documents worksheet shows each document’s associated subcontractor.

By using a Related row with a 2-way link, we can use additional Related row data types like lookups and rollups in both sheets.

Use Related row lookups to Collect Data from Other Records

Column E - Documents in our Subcontractors worksheet is a Related row lookup column that “looks up” the attachments in Column E - Document from our Documents worksheet for each related record in the adjacent column. The Related row lookup lets us collect all of the documents for each subcontractor in one cell.

Just like the original Attachment column in the Documents worksheet, we can expand and view the attachments in our Related row lookup column.

Use Related row rollups to Aggregate Data from Other Records

Column F - Latest Submission in our Subcontractors worksheet is a Related row rollup column that aggregates data from Column F - Date Submitted in our Documents worksheet. Related row rollups support a variety of aggregation functions. Here, we’re using the MAX function to calculate the latest date that a subcontractor submitted a document.

As subcontractors submit more documents, the Related row rollup will automatically recalculate the latest submission date.

Collecting Incoming Documents

By adding a Form view to our workbook, we can collect new document submissions from subcontractors or other consultants and automatically add the responses as new rows in our Documents worksheet.

Use a Form view for subcontractors to submit documents

By adding a Form view to our Documents worksheet, we can collect incoming documents directly from subcontractors whether or not they have a Spreadsheet.com account or access to our workbook.

Shared Form view where external parties can submit information directly to our workbook

The Form view has a shareable link that can be distributed to our subcontractors. When they open the form, they can select the type of document that they’re submitting and attach a copy of it.

As subcontractors use the form to submit documents, each submission will be added to our Documents worksheet as a new row.

Automatically log information with System data types

When new documents are submitted via the Form view, Column G - Date Submitted in our Documents worksheet automatically logs when the response was submitted with the Created at data type.

Notify team members with the User data type and Automations

By adding an automation to our workbook, we can automatically notify a member of our team when a new document is submitted via the form. When new documents are submitted, the Related row lookup in Column G - Assigned to automatically assigns them to the subcontractor’s point of contact listed in Column C - Internal Contact of our Subcontractors worksheet. When a new document is submitted, our automation will automatically notify that contact via email.

Automation created to notify a member of our team when a new document is submitted

In creating our automation, we can customize the notification message to include information directly from our worksheet, like the document type from Column C and the subcontractor name from Column A.

Automation notification customized with dynamic data taken directly from our workbook

Now, when a subcontractor or consultant submits a new document via the Form view…

  1. The new document is added to our Documents sheet
  2. The Documents worksheet logs the date and time of submission
  3. The document is linked to the subcontractor’s row in our Subcontractors sheet
  4. The new document is assigned to the appropriate point of contact
  5. That person receives an email notification alerting them that a new document has been submitted

…all without any manual data entry or additional user input.

Managing Document Approvals

As subcontractors submit new documents via our Form view, we can use additional Spreadsheet.com features like conditional formatting and row grouping to manage document approvals and easily identify documents that require our team’s attention.

Use the Select data type and Conditional Formatting to mark documents’ status

When new documents are submitted via our workbook’s Form view, they are automatically marked as “Not Reviewed” in Column H - Status of our Documents worksheet. This column is a Select column with additional options for “Approved” and “Rejected”. As members of our team review the incoming documents, they can update their status by choosing a new option from the Select dropdown.

The Select column is used to define our conditional formatting rules that change the row color based on the document’s status. Unreviewed documents are colored gray, approved documents are colored green, and rejected documents are colored red.

Documents worksheet with Conditional Formatting based on Column H - Status

When the status of a document is changed, the conditional formatting rules will automatically change the color of the row to reflect its new status.

Apply Row Grouping and Filters to Quickly Identify Action Items

By creating an additional Sheet view in our Documents worksheet and using filters and row grouping, we can easily identify documents that require attention. The “Action Items” view groups rows by Column H - Status and filters out those that are approved.

Documents worksheet grouped by Column H - Status

Ready to get started? Browse Spreadsheet.com’s Template Gallery to find ready-to-use document management templates, as well as templates for project management, sales, finance, and more.

Sign up — it's free