Author Topic: Update multiple live links  (Read 1816 times)

Offline en2jbn

  • Newbie
  • *
  • Posts: 2
Update multiple live links
« on: January 22, 2016, 04:02 PM »
Hello,

Is it possible to change the cell reference of all the live links associated with a diagram in one step?

For example - I have 20 flows in a diagram, these are all linked to row 1 of my excel table.
I want to produce the same diagram for a different set of flows which appear within row 5 of my table (all column references remain the same).
I can do this by editing the live links one-by-one, is there a way to edit all at once (maybe with a find and replace function?)

Many thanks

Offline pbeilschmidt

  • Administrator
  • Hero Member
  • *****
  • Posts: 509
  • e!Sankey - show the flow.
    • Umberto - know the flow.
Re: Update multiple live links
« Reply #1 on: January 25, 2016, 09:24 AM »
Hi en2jbn,

in such a case I typically edit the cell address for the Live Link reference directly in the table of the "Edit Live Links" dialog. There is presently no such thing as a find-and-replace feature in this table.

You can click into the field in the column 'Reference' (click two times in the field for inline editing, without performing a double-click) and overwrite the cell reference, e.g Table1!B5 to Table1!C5.

Another option would be to work on the Excel side and keep the cell reference stable, but copy the value from the field in the neighbouring cell (in the next column) to the cell that has the Live Link reference.

Are you aiming at a time series (e.g. creating 12 Sankey diagrams, one for each month, with the values located in 12 columns in the same row)? Let me know if this is the case, because there might be a different approach for this...

Peter
Peter Müller-Beilschmidt
e!Sankey Forum Moderator
ifu Hamburg GmbH

Offline en2jbn

  • Newbie
  • *
  • Posts: 2
Re: Update multiple live links
« Reply #2 on: January 25, 2016, 09:54 AM »
Hi Peter,

Many thanks for your reply.

I am aiming for a time series - I have flows for a number of years in adjacent rows. I would be interested to hear your approach to this.

Otherwise keeping the livelink cell reference stable and changing things on the excel side seems the best solution. I could use an index-match function or similar to get excel to look up the values for a given year.

Jonathan

Offline Jörn Zietz

  • Administrator
  • Hero Member
  • *****
  • Posts: 192
Re: Update multiple live links
« Reply #3 on: January 25, 2016, 03:54 PM »
Hi Jonathan,

I had an Excel file with different worksheets - each contained the values for a single year. To show these values with a single e!Sankey diagram, I added another worksheet. All my Live Links referred to this worksheet. On this worksheet I also entered the year, I wanted to show in my Sankey diagram. The year I used to refer to the correct worksheet. The function in Excel is called INDIRECT.

I build up a small example to transfer indirect references to columns instead of worksheets and attached it to this post. If you want to test the sample, download both files and copy them to the same directory (to allow updating the Live Link references). If you open the .sankey with e!Sankey, it asks if you would like to update the Live Link values. After you have done it, you open the Excel file via e!Sankey toolbutton. If you change switch to worksheet 'Live Link Values' and enter a number for 'Set Number' (between 1 an d 4) you see, that values on this sheets are updating. In e!Sankey the arrows are updated, after you activate e!Sankey.

If you want to create a batch of images or .sankey files by code, I suggest using e!Sankey SDK. It is e separate product, but it is really simple to update the values of e!Sankey diagrams. You can load your Sankey diagram and updates values with a CSV stream. And you also can generate Sankey diagrams by code (with a xml file oder stream). If this is interesting for you, please let us know.

Best regards
Jörn
Jörn Zietz
e!Sankey Team
ifu Hamburg GmbH