Visio calculating shape data

[mattwyre]

Hi,

I'm after some advice on how to calculate imported excel data. If I have one shape and I want to calculate two pieces of data from two separate shapes into it, what is the method to do this? If it were excel you would simply create a sum between two cells and it would work it out. In Visio it doesn't appear to be that straightforward.

The shape data field is the same for each shape but has different values used from the imported excel sheet. It is these values I want to total, into a field, in a separate shape.

Hope this makes sense, look forward to any help and response.

Thanks,
Matt

[vojo]

if the 3 shapes are static / defined apriori...not hard
- Open calculating shape sheet
- Input formula
    - create a cell called calc in the user section...lets call it user.calc
    - user.calc = sheet.1!user.input1 + sheet.2!user.input2
         - note sheet.1 is name for shape 1.....sheet.2 is the name for shape 2
         - You can open input shape shapesheet to find actual names and create input cells
- As a result, calculating shape now has the inputs and does the calculation

However, if the shapes are dynamic / can be dropped at any time....this is much more difficult
You will need VBA since shapes can NOT discover other shapes on their own.

If you know its finite number of shapes involved, you can do this
- at page shapesheet, create a bunch of user cells
     -  N data cells
     -  N handle cells to define handles
- For each input shape, create user.data  and user.hanlde
     - data has the input values
     - handle is the handle to look for in page shapesheet
     - create search shape
          - something like:   If(page!user.handle1 = XX, setf(getref(page!user.data1),if(page!user.handle2 = YY, setf
          - Max is 127 If then elses.
- for calc shape, do similar
      - user.localinput1 =if (page!user.handle1 = XX, page!user.data1,if(page!user.handle2 = YY, page!user.data2
      - user.localinput2 = <Same sort of thing>
      - User.calc = user.localinput1 + user.localinput2

Again not totally flexible and only for finite cells.   need to work thru handles and such.
Very subtle so start small

[vojo]

June the 2nd a long time ago had a family of shapes that did this sort of thing....may want to ping him