Templates for Continuous Process Improvement

 

How to use a shared data table

Purposes of a shared data table

VC sheet tab
  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

 

The following Systems2win Excel templates have a shared data table:


Bookmark = 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'

Hide Show Rows button

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

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

Work Instructions ribbon in the Systems2win menu

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

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

Ideal light bulb
  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.

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


Bookmark = 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).

Renumber Steps

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)

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.



Bookmark = Personalize

Personalize Header Fields on your VC sheet

These notes are more for Systems2win than for you, because when you own 4 seats or more,
Systems2win transfers your personalizations for you each time that you upgrade

 

Video Help

Version Control video

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 worksheet itself, or in the top header section of any sheet in the workbook.

Do not move or delete header text boxes.

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 — Free

For all of our customers that own 4 seats or more, 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"