[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


MS Visio: How to synchronize multiple cells beetween beetween multiple master instances in different pages?

Posted on 2016-08-12
Medium Priority
Last Modified: 2016-09-07
I have build a system in Visio 2013 back ago, and now my client wants to synchronyze multiple cells beetween multiple master instances in different pages. Each page has 100+ master instances, each master has 10+ cells to be synchronized. So in each master's cell I entered a formula like this:

and so on.

that works well to the synchronize purposes, but it will take a LOT of time to synchronize all the master instances.

I want to take the approach to make the "MasterInstanceID.000001" a variable in the formula, related to a User Defined cell, so I only  have to change the 000001 number once in each master instance, and not in each shapesheet cell, when I have had applied the changes in the master, and all the master instances being actualized. I can't use the EVALTEXT() function, because I'm not allowed to change the text displayed by the master instance.

Any ideas? Best regards.
Question by:Gustavo Garcia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 31

Expert Comment

by:Scott Helmers
ID: 41754884
Unless I've missed what you're trying to accomplish, I think there is a much more efficient way to achieve the desired result. Am I correct that what you're trying to do is to change one value and then have all instances of that master, regardless of the page on which they are located, reflect the new value?

If so, try this:
  1. In the document ShapeSheet for the stencil, create a user cell, let's call it User.MasterValue1
  2. In each master in the stencil, create a Shape Data cell, let's call is Prop.MasterValue1, and set its value to =TheDoc!User.MasterValue1

When you drag a master onto a page, it's Prop.MasterValue1 cell will automatically reflect the value in the document ShapeSheet. When you change the value in the doc sheet, all instances that refer to it will be updated automatically.

Re #1 above, to get to the ShapeSheet for the stencil document:
  • Right-click the header bar for the stencil and then select Drawing Explorer Window
  • In the Drawing Explorer window, right-click Stenci3 (or whatever name appears at the top of the window), and then select Show ShapeSheet

NOTE: You can create User.MasterValue1 in the doc sheet for the actual diagram, but it isn't necessary. The first time you drag one of your masters onto any page in the document, Visio will create a doc sheet cell for you because the master contains a formula that refers to the doc sheet.

Author Comment

by:Gustavo Garcia
ID: 41756687
Hi Scott! Thank you for your answer, but that's not what I'm trying to accomplish.

I'm trying to synchronize a group of cells in several instances of master 1,  let's call it a SourceMaster. The instances are already dragged to page 1 of a Visio document, and with useful data, with a other group of cells in other several instances of master 2, let's call it a RecipientMaster. RecipientMaster instances are already dragged and placed in page 2 of the same document. I want to synchronize cells of a particular SourceMaster instance, with a particular RecipientMaster instance.

Since there are several instances to synchronize and each instance is already placed, I want to explore the posibility to make the unique ID number of SourceMaster instances reference a "variable" somehow in the synchronization formulas in RecipientMaster instances cells, so once I've edited the RecipientMaster, and updated all its instances, I only have to change the referenced ID number of the SourceMaster instance,  to synchronize all the cells of the instance with synchronization formulas. As far as I've tried, I haven't  found a method to accomplish this. Please help!
LVL 31

Expert Comment

by:Scott Helmers
ID: 41764676
As a general answer, you can't use variables in ShapeSheet formula. However, there is probably still a way to accomplish what you want...

Are your relationships one to many? Or one to one? For example in the diagram below do the cells in A need to be synced with W, X, Y, and Z? Or A to W, B to X, etc.?shape exampleEither way, it's likely that using doc sheet or page sheet cells as the communication vehicle might still work. For example, the SETATREF() formula in the source masters could reference a doc sheet cell instead of a cell in a specific shape, and then the cells in the recipient masters could simply be equated to those same doc sheet cells. That way any time you make a change to a value in a source instance, the corresponding doc sheet cell will get updated, which will automatically change the value in all corresponding recipient instance cells.

Am I getting closer?
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

by:Gustavo Garcia
ID: 41766077
Hi Scott, thank you for your answer. You're getting a step closer, but not still there yet. My relationships are one cell to one cell for different master instances. Using a doc or page user cell to sync each cell in this particular case is still impractical, because I've in this particular system around 700 master instances and I need to synchronice about 5 to 7 cells in each instance. Making that amount of user cells references in a page or doc would be still too data capture intensive.

I already made a report with the sheetID of each master instance I have to sync, and they wont change in the future, that means that the cell's relationships are static and well known. Right now, with the approach you suggested I've been playing with some user cells  and tunneling techniques, like triple """ """ quotation marks, trying to make the sheetID of master instances to sync a "fixed" variable for the particular master instance, but still I had no luck.
LVL 31

Assisted Solution

by:Scott Helmers
Scott Helmers earned 2000 total points
ID: 41766246
Knowing that you have several thousand cells to sync certainly changes the options for what you're trying to do.

With that volume of relationships, and if the relationships don't change, why not write VBA code to build all of the formulas for you? That would eliminate thousands of SETATREF() formulas and the attendant performance hit you've described.

It would take a little while to put the code together, but then it would be easy to tweak it as need to finalize the formula. And making changes in the future would be dramatically simpler as well.

Without knowing what your Excel report contains, but assuming that you've got all of the appropriate shape IDs and cell names, I would suggest writing code to 1) read relationship pair data from the Excel workbook, and then write formulas into shapes.

Author Comment

by:Gustavo Garcia
ID: 41775492
Hi Scott! Thank you for your comment. I was trying to avoid a solution that implies to write a extra code just to do that, but it seems there are no other way. I'm not well versed in Automation yet. Can you point me to the procedures used to write a formula in a cell from excel?

Best regards
LVL 31

Accepted Solution

Scott Helmers earned 2000 total points
ID: 41778347
I understand about not wanting to write code if you don't need to, but I think it may save you a lot of work in the long run. Also, keep in mind that the diagrams you distribute won't necessarily need to include the code -- from what you've described, the code should only be necessary for setting the formulae into the shapes. After that the diagram should be able to live on its own.

Two suggestions for getting started:
1) Fellow Visio MVP John Goldsmith posted an intro for Visio developers on his excellent blog.
2) I created a three-part series called "Becoming a Visio Power User" for Microsoft Virtual Academy. Part 3 is all about getting started writing code. It contains a lot of small VBA examples that should be helpful.

Beyond that, keep posting questions here.

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question