Skip to main content

Excel Templates, Training, and Systems

How to use a shared data table

Purposes of a shared data table

  1. To serve as one central place to update data fields that appear on multiple sheets in the workbook.
  2. To make it possible to integrate with third-party version control software
  3. To make it possible to automatically transfer your personalized header fields to your new templates each time that you upgrade
VC sheet tab

 

The following Systems2win Excel templates have a shared data table:

If your Work Instructions template still has a VC sheet, and your Annual Maintenance is current, simply send us a copy of your most recent master template, and we will send you a (personalized) new master template — free.


ID

ID Table

The purpose of the ID Table in your Work Instructions template

is to make it super easy, fast, and accurate to update Work Instructions for multiple items in a product family

When you use either of the buttons to 'Hide or Show Rows' or 'Generate Report'

then any header fields on your Master Data Entry Sheet that are marked with an asterisk (*) will be populated with data for your selected ID Code

Hide Show Rows button

On your Master Data Entry Sheet

(that appears when you click the button to 'Open a Blank Sheet')

for any header field that is marked with an asterisk (*) the data for that field must be entered in the ID Table on the DV sheet
(which contains all of the data that is shared by every sheet in the workbook)

 

Work Instructions ID Table

ID Table on the DV sheet of your Work Instructions template

If you use this table for optional ID Codes within a product family, then the top row is for 'All' of the product family, and the (optional) table is for your parts within the family.

In the ID Table on your DV sheet

Data is required in the top row, for either :

  1. The overall Product Family This is the data that will appear when you select the radio button for 'All' when you use the macro buttons to 'Hide / Show Rows' or 'Generate Report'
  2. A simple product or process that doesn't have any optional ID Codes in the table below
Work Instructions ribbon in the Systems2win menu

Data is optional in the table (below the gold column headers) for:

  1. Products or parts in a product family
  2. Any types of services or customer deliverables

Although the most common use of ID Codes is for 'parts', your ID Codes might be services within a service family, or any types of customer deliverables that have many Steps that are performed for every one of them, and yet some steps that are different for different types of deliverables in the family.

Ideal light bulb

Once the light bulb flashes in your head...

you're going to suddenly realize just how much time this tool is going to save you when you're the person responsible for the work instructions for your product families


StepLabel

Step Label

When you click the Renumber Steps or Generate Report button in your Work Instructions template...

it will automatically renumber your steps
(in the wide column that also contains images).

Exception:

Auto-numbering will skip any cell in the Steps/Images column that contains text that does NOT start with the same text as the Step Label field that you define on the DV sheet.

(VC sheet in the old version)

Renumber Steps

Benefit of this feature:

This allows you to intentionally skip step numbering perhaps to provide additional comments or training for a complex sub-step.

Potential gotcha: If you change the Step Label field after you already have some numbered steps — you will need to manually delete the old numbering before clicking the Renumber Steps button (because the old number labels will start with the old text — which will no longer match the new text, and will therefore be skipped).


Integration with Version Control software

Your shared data table makes it possible to integrate with most third-party Version Control software.

The version control software updates the data in just one place (the VC or DV sheet) which then auto-updates all sheets in the workbook.

Exception: On any sheet, you can purposefully overwrite the formula linking to the default data on the VC sheet replacing that formula with static text or data that won't change

Your version control software might then automatically generate a controlled document in PDF format and store it in your usual document control vault.


testimonial quote

Personalize

Personalize Header Fields on your VC sheet

These notes are more for Systems2win than for you, because when you own the Personalization Upgrade Transfer feature, Systems2win transfers your personalizations for you each time that you upgrade

 

You can personalize your VC field labels, field label text boxes, and default data in ways that are automatically found and transferred to your new templates each time that you upgrade.

It is okay to hide rows, but do not insert, move, or delete rows on the VC sheet itself, or in the top header section of any sheet in the workbook.

Do not move or delete header text boxes.

Video Help

Version Control video

When upgrading from a legacy template that was purchased after April 2012, the following will be automatically found and transferred to your new templates each time that you upgrade:

On the VC sheet — in the Header Fields — User Defined section:

  • Header Labels
    (a field on the VC sheet that has a Name defined by Systems2win that starts with LabelUH)

    Formulas and Formats

  • Header Default Data
    (a field on the VC sheet that has a Name defined by Systems2win that starts with UHeader)

    Formulas, Formats, and Validation

    Tip: When Excel copies 'Formulas', it will instead copy the data for any cell that contains data rather than an actual formula

    Tip: Formats include Wrap Text, Shrink To Fit, font size, font weight, font color, and more

    Tip: Validation can restrict acceptable data, and can present a dropdown list from the DV sheet

    Tip: See Idea Starters for User-Defined Header Fields — in the text box below

On the VC sheet — in the Default Data section:

  • Default Data: Formulas
  • Default Data Labels: User Substitutions only
    (see explanation in the 'Header Fields - Standard' section below)

On the VC sheet — in the Header Fields - Standard section:

  • Header Labels will NOT be automatically found and transferred to your new templates each time that you upgrade, but (just like any other field in any Systems2win template) you CAN use User Substitutions to substitute your preferred text — which WILL be automatically found and transferred to your new templates each time that you upgrade.
  • Default Data will NOT be automatically found and transferred to your new templates each time that you upgrade.

On the Template sheet —

  • Header Data for User-Defined fields
    (a field that links to the VC sheet that has a Name defined by Systems2win that starts with VCUData)

    Formulas, Formats, and Validation are automatically found & transferred

  • Header Labels for User-Defined fields
    (a field that links to the VC sheet that has a Name defined by Systems2win that starts with VCULabel)

    Formulas, Formats, and Validation are automatically found & transferred

If you made any additional changes not specified here, then be sure to use your Customization Log as a reminder to make those same changes after each upgrade.

And whenever you do an upgrade, you should always manually inspect any template that you know that you have personalized.

Changes that you made to headers on old templates might need to be manually recreated when upgrading:

Full Service Transfer of your Personalizations

For all of our customers that own the Sync feature, we have been going the extra mile...

Not just running your personalized master templates through our automated Personalization Upgrade Utility that automatically finds and transfers most common personalizations,

but also providing full service transfer of ALL of your personalizations each time that you upgrade (including ones not automatically found and transferred).

Your success is important to us, and this is one more way that we have been saying, "Thank you for being a long-time valued customer"

 

 

 

150+ Templates
Try Some Own Them All

 

 

 

 

 

New User Training

Your Systems2win templates come with free New User Training
to quickly learn features that are common to all 150+ templates