This is a pictorial description of the Excel LiveLink, which is an exclusive feature in e!Sankey 2.0 pro.
With this feature a 'live' connection from one or more Excel spreadsheets to an e!Sankey diagram can be established. When changes are made in the spreadsheets connected to the e!Sankey diagram file, it can be updated (automatically or manually), so that the arrow widths and the text labels in the diagram show the new values.
Excel LiveLinks can only be created for existing diagram elements. Drawing new diagram elements with this feature is not possible.
The Excel LiveLink works for the following versions of Microsoft Excel: Excel XP (2002), Excel 2003, and Excel 2007.
Excel Live Link for Numeric Values The Excel LiveLink dynamically links the quantity value for a flow in an existing diagram file in e!Sankey with a cell in an Excel spreadsheet. When the user changes the value in the cell of the Excel sheet, the value in the Sankey diagram will be updated automatically and the arrow is redrawn accordingly, so that its magnitude represent the new value.
Figure 1: Manually specified flow, no LiveLink has been created yet
A LiveLink is created by copying a cell value in Excel (Ctrl-C) and pasting it (Ctrl-V) in the „Quantity“ field in the Arrow properties panel. To do so, the arrow must be selected, before the cell content is pasted. A LiveLink reference to the cell in the spreadsheet will be created. An icon in the column is shown, to indicate the status of the LiveLink.
Figure 2: A LiveLink has been created from a cell in the Excel sheet.
The status of the LiveLink is shown in the „LL“ column with different icon.
- Valid: the referenced Excel document was found, but is not open. The live link is valid, and will be updated automatically.
- Valid, Manual Update: the referenced Excel document was found, but Excel is currently not running, the document is not open. Manual update is possible.
- Valid Live: the referenced Excel document was found and is open. e!Sankey can perform automatic updates.
- Broken: e!Sankey couldn't find the referenced Excel document.
- Invalid Value: the referenced cell in the Excel document contains a value that can not be converted into a numeric figure. The last known figure will be used.
A double click on the icon in the 'LL' column opens the 'Edit LiveLink' dialog (see below).
There are two options for updating values in the Sankey diagram:
- Automatic: After each operation (e.g. a change of a value in a cell), the Sankey diagram will be updated via the LiveLink. This is the default setting.
- Manual: Values will only be updated, when the Update command is executed by the user.
Figure 3: The value in the cell was changed, the value is automatically updated in the Arrow property dialog....
Figure 4: ...and in the e!Sankey diagram
There can be multiple LiveLinks from one e!Sankey diagram to cells in one or more Excel sheets. An overview of all LiveLinks can be seen in the 'Edit LiveLinks' dialog. Each LiveLink entry can be edited. Furthermore LiveLinks can be updated manually, checked and removed (broken) from this dialog.
Figure 5: Edit LiveLinks dialog shows the Excel files to which LiveLinks have been created
Excel LiveLink for Text String Additionally to the LiveLink for numerical values, text strings from Excel sheets can also be linked to text elements in the e!Sankey diagram file.
This can be done for Text Labels, Process Labels and Arrow Comment Labels
Note: Arrow Labels are created automatically from the entry name, the quantity, and the unit. They are not editable, thus can not be updated via an Excel LiveLink.
Figure 6: LiveLinks from Excel are connected to text labels and arrow comment labels
A LiveLink for a text string is created by copying a cell value in Excel (Ctrl-C) and pasting it in the text label, process label or arrow comment field using the Paste Live Link button below the text field. The LiveLink is shown by the active LiveLink icon.
Figure 7: Text field in property dialog and the buttons for pasting, editing and removing a LiveLink for text labels.
Note: Pasting the content of the clipboard with Ctrl-V will just paste the content as normal text, but will not set the Live Link!
Figure 8:Updated text strings in Excel show up directly in the e!Sankey diagram
To remove a live link connection, click on the button Remove LiveLink below the text entry field of a text label, process label or arrow comment.
To edit a live link for a text string, click on the button Edit LiveLink below the text entry field.
Using Names in Excel to Maintain LiveLinks to CellsWhen a LiveLink is created to a cell in an Excel sheet, the reference is by default made to the cell ID (e.g. “C1”). However, if the spreadsheet layout is changed the location of the cell with the value that is linked to the diagram in e!Sankey might be shifted. This happens, for example, when columns or lines are inserted.
In order to maintain the LiveLinks even when the location of the original cell, thus its cell ID, changes, it is necessary to work with named cells. Before you create the LiveLink as described above, name the cell (in Excel: Menu Insert > Names > Define). When a Live Link is created, it will use the name of the cell instead of the cell ID. The named cell can be located anywhere in the Excel sheet.
Figure 9: The linked value in the named cell “Link1” is moved from B1 to C3 because a column and two lines were inserted. The LiveLink is maintained, because it references the name, not the cell ID
When the value in the named cell is updated, the LiveLink will still work even though it might have a different location.
