How to use a shared data table
Purposes of a shared data table
The following Systems2win Excel templates have a shared data table:
- The shared data table for the FMEA, and Control Plan templates are on a VC worksheet
- The shared data table for the Work Instructions template used to be on a VC worksheet
and then it was moved to the DV sheet
in order to better support data shared by multiple products within a product family.
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.
Bookmark = ID
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
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)
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 :
- 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'
- 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:
- Products or parts in a product family
- 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
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).
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 the Personalization Upgrade Transfer feature,
Systems2win transfers your personalizations for you each time that you upgrade
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 sheet 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:
- from templates older than May 2012
- from the old JobInstructions.xls template or WorkInst.xlsx template
(because in April 2016, the VC sheet was replaced with the ID Table on the DV sheet)
- from the old StdWk.xls template
to the (new as of December 2015) StdWork.xlsx template
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"