In this post, I’m going to look at one method to provide a lookup in a 2D array, which could be used, for example, to map cells in the ShapeSheet to a table collection.
When building Visio shapes containing a collection of sub-shapes, a common pattern is to build a single sub-shape with an index cell (either User. or Prop.) and then hang all other sub-shape logic off that index.
You can then set a series of list cells in the hosting group shape that the sub-shapes can interrogate to retrieve their correct value.
For example, here I've defined Color and Y position lists (separated by a pipe character) in a traffic light group shape:
These lists can then be accessed by the light sub-shapes like this:
By changing the Idx cell, the sub-shape reacts accordingly:
With the logic in place for the first sub-shape you can then easily duplicate this 'source' sub-shape and just set the index cell as required. [Note – you have to surround the Width, Height, PinX and PinY cells with a GUARD function prior to duplicating otherwise the formula logic will be lost].
One of the benefits of this method is that if you later need to change, say the RGB values, you just have a single cell that you need to edit rather than having to trawl through each of the sub-shapes in turn. If you need to fundamentally change the logic of the shape you can easily delete all but one of the sub-shapes, make your changes and then re-duplicate the other shapes, which now then new logic.
2D Array
So the above is just based on a single index cell, but recently I was creating a table grid shape for a customer and wanted to implement a 2D lookup so that I could map the row and column values in a table to an easy to edit set of cells.
Visio has had the Scratch ShapeSheet section for a long time and it pre-dates the User section, which you might think of as a more natural fit for ‘variable’ type data.
One advantage of the Scratch section is that it is already a grid structure, albeit with a limited number of columns, and so lends itself the kind of 2D lookup that I’m after. Not only that, but it also has a fixed naming system (unlike User cells), and this makes it easier to address.
So I started off with the hosting group shape and set the values that I want to appear in the table in its Scratch section. In my scenario I don’t need more than four columns and the rows can be expanded as required:
I then add a single sub-shape that contains User cells to determine its Row and Column:
Next is where the SETF comes in. If you look at the User.TextTrigger cell you can see (below) the following formula:
=SETF(GetRef(User.Text),
"Sheet."&Sheet.1!ID()&"!Scratch."&User.ColAsLetter&User.Row+1)
What this is doing is building up the Scratch cell reference and mapping the Column integer value to the corresponding letter. This is then pushed into User.Text, which is in turn used in the shape’s text field.
As the other sub-shapes are generated using the duplicate + set index method, I mentioned earlier, (or at least setting the Row and Column values), the User.TextTrigger cell is fired and the correct value is again pushed into the User.Text cell resulting in the entire table becoming populated:
The big advantage here is that you’ve now got a single place to edit your entire table and this can either be with straight text values as above or, with references to other cells, perhaps in the Shape Data section.
Other uses
This technique isn’t just limited to tables, but also allows you to store 2D data that can be used dynamically in the same way that INDEX lookup lists work. It’s worth saying that you could do this kind of look up using a combination of other formulas but what appeals to me about this method is that it separates the lookup logic from the data and makes it easier to see and therefore maintain in the future.
If you’re interested in this sort of technique, of embedding lookup data in shapes. then you might be interested in David Parker’s take on using JSON to achieve something similar:
https://blog.bvisual.net/2018/02/13/using-json-text-in-visio-shapes/
