Author Topic: Description of the Excel LiveLink feature in e!Sankey 2.0 pro  (Read 19759 times)

Offline pbeilschmidt

  • Administrator
  • Hero Member
  • *****
  • Posts: 509
  • e!Sankey - show the flow.
    • Umberto - know the flow.
This is a pictorial description of the Excel LiveLink, which is an exclusive feature in e!Sankey 2.0 pro.
Images are missing after moving forum to new server, please download the full post as PDF to see embeded images (download link in reply below)

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 Cells

When 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.
« Last Edit: December 22, 2011, 11:27 AM by pbeilschmidt »
Peter Müller-Beilschmidt
e!Sankey Forum Moderator
ifu Hamburg GmbH

Offline pbeilschmidt

  • Administrator
  • Hero Member
  • *****
  • Posts: 509
  • e!Sankey - show the flow.
    • Umberto - know the flow.
Re: Description of the Excel LiveLink feature in e!Sankey 2.0 pro
« Reply #1 on: December 22, 2011, 11:24 AM »
Somehow the embedded images in the post have gone missing. Please download a PDF copy of the description here instead (see page 38).
« Last Edit: February 08, 2013, 10:51 AM by jbakenhus »
Peter Müller-Beilschmidt
e!Sankey Forum Moderator
ifu Hamburg GmbH