5 Tips for Suppliers Who Manage Project Documentation with Excel

While not the ideal solution, many companies manage project documentation using Excel. Here are 5 quick tips to consider if you are among them:

1. 1. Build the entire document register upfront.
      Without this step, you are just flying by the seat of your pants.

       

      In DocBoss, we rely on a key phrase:
      We must supply ONE {docname} for every {tag, unit, drawing, heat number etc..}.
      i.e. “We must supply one {instrument data sheet} for every {tag}”

      This simple phrase will help you determine exactly how many registers to create for each type of document.

1. 1. Use mail merge from the supplier document index (SDI)

      With a small programming effort in excel, you can create a column in your SDI where you enter a “Y” to indicate you want to submit them. With a button push, you can create a word doc with all of your cover pages filled in while simultaneously marking the docs as transmitted in your SDI. Still lots of work left to PDF/and merge, but your SDI will never be out of date (on sending anyway). Not an excel/ programming wizard – post a project here [link to elance]. I’d bet you can get it built for under $1000.

1. 1. Have a “status” column which shows whether the register is active or not.

      Your order will change. When you update your register (maybe this doc is no longer required – or that doc is now contained in this one) you will be able to tell your customer what has happened to each register. Don’t delete them! Color is a good way (turn the row grey), or user strike through if they are no longer accurate.

1. 1. Ensure your SDI (Supplier Document Index) remains accurate after change orders.

      Changes to tags, quantities etc wreak havoc on the SDI. When you have a change order, head right over the the SDI, and update all effected registers. Change tag numbers, add new registers etc. CTRL-H is a good tool here. See number 3 if you don’t need a register anymore.

1. 1. Use VBA to make your inbound transmittal folders.

      Yes – kind of a corollary to number 1, but this help in the opposite direction. There is a very small piece of code you put under a button in excel (call it, for example, “incoming transmittal”). Before just dumping docs into a folder for later processing, put them into a “pending” folder. Then open your SDI, and put a “Y” in the column from #1. Then click the button. Your SDI can be automatically updated, and the folder created automatically. If make yourself a rule to never create doc folders manually, you’ll always have an up to date SDI.

Or I guess, buy DocBoss.

Here are a few more Excel-related articles that might interest you:

4 Quick Tips For Using Excel For Your Document Control Work

Converting A Word Document To Excel

4 Quick Tips for Using Microsoft Excel and SDIs