Issues with Structured Data Input and Consolidation
If you need to collect structured input from colleagues (e.g., you need to consolidate data from business units), you are experiencing some of the following issues:
- You have to create a spreadsheet template, clone it and send it out
- You do not know whether your contributors received it or are working on it
- Contributors may alter the layout
- Contributors may (even accidentally) alter formulae
- Contributors send their spreadsheets, usually on the day of the deadline
- You spend a lot of time to audit, under time pressure, the consistency of the contributors’ spreadsheets
- You run script to consolidate them and create reports
Templating in Visyond
Let’s start by preparing the a simple template to share with Contributors.
Switch to Audit Mode to see the auto-formatting as in the exhibits below.
You want to set some of the input cells and only allow contributors to edit certain cells.
Select the range A3:C3 and A6:C6 and click on the M (for “Mark as editable”) button.
Now you can invite contributors by clicking on Template button.
Every time you invite a contributor (e.g., a colleague in a department or subsidiary), Visyond creates a clone of the worksheet with a name you assign.
The contributor will receive a link via email that will take her to a Visyond page(see image below) where:
- She can only see her worksheet. A contributor has no access to other contributors’ worksheets or your consolidated data.
- If she tries to edit a cell you have not marked as editable, he will not be able to (see image below taken while trying to edit cell C2).
This gives you full control on the layout and the data entry. If you want one of the contributors to see everything, just change his permission to Collaborator.
Consolidating Data in Visyond
Now sit back, relax and wait for your Contributors to complete the data entry. Once done, select the template worksheet (the one you cloned for Contributors) and click on the “C” (consolidation)button next to the Template button.
Visyond will create a consolidated worksheet where, by default, each cell is the summation of all the corresponding cells in the Contributors’ worksheets.
Using Templating for “Private” Unstructured Input Gathering
You can use the templating workflow to collect assumptions from contributors without them seeing anything but the worksheet shared with them:
- Create various worksheets (with different structures) with different assumptions you want to gather from different contributors.
- Invite contributors as described in the section above.
- Have the formulae in your calculation worksheet linked to the worksheets filled by the contributors.