Author Topic: Can you clone shapes to another sheet?  (Read 1878 times)

0 Members and 1 Guest are viewing this topic.

Requin

  • Jr. Member
  • **
  • Posts: 10
Can you clone shapes to another sheet?
« on: August 14, 2018, 10:26:30 AM »
I was reading up on http://visguy.com/vgforum/index.php?topic=6856.0 and found that you could use ctrl + d to duplicate the item to maintain the formulas.  But I will need to move these to a new sheet. Is there an easy way or do I have to setup special code in the formula.

Current code
Code
=IF(FORMULAEXISTS(Fibre Cable!Prop.DeadCount)=FALSE,TRUE,Fibre Cable!Prop.DeadCount&CHAR(10))&"FC"&Fibre Cable!Prop.FDSA&","&SETATREF(Fibre Cable!Prop.Count1)&"-"&Fibre Cable!Prop.Count2&CHAR(10)&Fibre Cable!Prop.CableType&" "&Fibre Cable!Prop.CableSize&"/"&IF(Fibre Cable!Prop.Underground=1,"BUR","AER")

Code after moved to a new sheet (no longer updates automatically)
Code
=IF(FORMULAEXISTS(REF())=FALSE,TRUE,REF()&CHAR(10))&"FC"&REF()&","&REF()&"-"&REF()&CHAR(10)&REF()&" "&REF()&"/"&IF(REF()=1,"BUR","AER")

Surrogate

  • Hero Member
  • *****
  • Posts: 1094
    • ShapeSheet™ Knowledge Base
Re: Can you clone shapes to another sheet?
« Reply #1 on: August 14, 2018, 11:13:49 AM »
Of course, if on another page (that you mean as sheet) not exist shape with name "Fibre cable" you get REF() in your formula
« Last Edit: August 14, 2018, 01:43:59 PM by Surrogate »

Paul Herber

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2978
    • Paul Herber's website
Re: Can you clone shapes to another sheet?
« Reply #2 on: August 14, 2018, 11:20:14 AM »
... so if you copy the shape "Fibre cable" first ...
Electronic and Electrical engineering, business and software stencils and applications for Visio -

https://www.paulherber.co.uk/

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: Can you clone shapes to another sheet?
« Reply #3 on: August 14, 2018, 12:42:10 PM »
... so if you copy the shape "Fibre cable" first ...

Didn't work.  If I use ctrl + D to duplicate it works on the same sheet but when copy pasting it doesn't even work on the same sheet.

https://www.youtube.com/watch?v=52QszfXrYC0&feature=youtu.be is what I want to do but unsure how to add that to the formula to make it work.

Surrogate

  • Hero Member
  • *****
  • Posts: 1094
    • ShapeSheet™ Knowledge Base
Re: Can you clone shapes to another sheet?
« Reply #4 on: August 14, 2018, 01:42:49 PM »
You are right, jotkey Ctrl+D works only in same page (sheet)!
But you can copy shape and paste in to another page.

UPDATE: it is very strange formula
Code
IF(FORMULAEXISTS(Fibre Cable!Prop.DeadCount)=FALSE,TRUE,Fibre Cable!Prop.DeadCount&CHAR(10))&"FC"&Fibre Cable!Prop.FDSA&","&SETATREF(Fibre Cable!Prop.Count1)&"-"&Fibre Cable!Prop.Count2&CHAR(10)&Fibre Cable!Prop.CableType&" "&Fibre Cable!Prop.CableSize&"/"&IF(Fibre Cable!Prop.Underground=1,"BUR","AER")
can you share document with example where this formula works ?
« Last Edit: August 14, 2018, 01:59:07 PM by Surrogate »

Paul Herber

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2978
    • Paul Herber's website
Re: Can you clone shapes to another sheet?
« Reply #5 on: August 14, 2018, 02:04:29 PM »
and ensure that after pasting you rename it back to the original shape name.
If you just copy and paste to the new page then the shape will keep its name.
Electronic and Electrical engineering, business and software stencils and applications for Visio -

https://www.paulherber.co.uk/

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: Can you clone shapes to another sheet?
« Reply #6 on: August 14, 2018, 02:43:43 PM »
I can't share the whole document but I can share the result of that code.


FC9901A,24-36
5BDXLT 48/AER


Paul I don't think the copying and renaming the shape will work either because this has to be used by some very computer illiterate people.

Yacine

  • Hero Member
  • *****
  • Posts: 2713
Re: Can you clone shapes to another sheet?
« Reply #7 on: August 14, 2018, 03:25:03 PM »
I think that the references are kept if you copy and paste both the reference and the target shape in one operation.

If this doesn't help, you can make a group of the 2 shapes, then copy and paste to the new page.
Yacine

Surrogate

  • Hero Member
  • *****
  • Posts: 1094
    • ShapeSheet™ Knowledge Base
Re: Can you clone shapes to another sheet?
« Reply #8 on: August 14, 2018, 03:33:42 PM »
1. You need use reference to another page with syntax like - Pages[<PageName>]
2. You need use reference to shape with name Fibre Cable with syntax Sheet.<ID of Fibre Cable shape> instead its name

at my side I use formula
Code
IF(FORMULAEXISTS(Pages[tst]!Sheet.2!Prop.DeadCount)=FALSE,TRUE,Pages[tst]!Sheet.2!Prop.DeadCount&CHAR(10))&"FC"&Pages[tst]!Sheet.2!Prop.FDSA&","&SETATREF(Pages[tst]!Sheet.2!Prop.Count1)&"-"&Pages[tst]!Sheet.2!Prop.Count2&CHAR(10)&Pages[tst]!Sheet.2!Prop.CableType&" "&Pages[tst]!Sheet.2!Prop.CableSize&"/"&IF(Pages[tst]!Sheet.2!Prop.Underground=1,"BUR","AER")
And it works!

Yacine

  • Hero Member
  • *****
  • Posts: 2713
Re: Can you clone shapes to another sheet?
« Reply #9 on: August 14, 2018, 04:32:23 PM »
@Surrogate, clever to keep the reference on the orginal page 8).
Yacine

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: Can you clone shapes to another sheet?
« Reply #10 on: August 15, 2018, 03:18:27 PM »
I had tried what Surrogate had mentioned but since the formula was on the same page as it was referencing (when starting) it shortens the formula to not include the pages. 

Is there a way to override that?

Surrogate

  • Hero Member
  • *****
  • Posts: 1094
    • ShapeSheet™ Knowledge Base
Re: Can you clone shapes to another sheet?
« Reply #11 on: August 15, 2018, 04:46:27 PM »
At my side this complex formula must be stored in cell User.Row_1. As first I set this cell empty, and in cell User.Row_1.Prompt I write more complex formula with SETF function
Code
=IF(STRSAME(PAGENAME(),"tst"),SETF("User.Row_1","IF(FORMULAEXISTS(Fibre Cable!Prop.DeadCount)=FALSE,TRUE,Fibre Cable!Prop.DeadCount&CHAR(10))&""FC""&Fibre Cable!Prop.FDSA&"",""&SETATREF(Fibre Cable!Prop.Count1)&""-""&Fibre Cable!Prop.Count2&CHAR(10)&Fibre Cable!Prop.CableType&"" ""&Fibre Cable!Prop.CableSize&""/""&IF(Fibre Cable!Prop.Underground=1,""BUR"",""AER"")"),SETF("user.row_1","IF(FORMULAEXISTS(Pages[tst]!Sheet.2!Prop.DeadCount)=FALSE,TRUE,Pages[tst]!Sheet.2!Prop.DeadCount&CHAR(10))&""FC""&Pages[tst]!Sheet.2!Prop.FDSA&"",""&SETATREF(Pages[tst]!Sheet.2!Prop.Count1)&""-""&Pages[tst]!Sheet.2!Prop.Count2&CHAR(10)&Pages[tst]!Sheet.2!Prop.CableType&"" ""&Pages[tst]!Sheet.2!Prop.CableSize&""/""&IF(Pages[tst]!Sheet.2!Prop.Underground=1,""BUR"",""AER"")"))
If this shape on same shape this formula set formula to cell User.Row_1 without Page's reference, for another page this formula set formula with Page's reference
« Last Edit: August 15, 2018, 04:53:51 PM by Surrogate »

Nikolay

  • Hero Member
  • *****
  • Posts: 1007
    • UnmanagedVisio
Re: Can you clone shapes to another sheet?
« Reply #12 on: August 15, 2018, 05:25:27 PM »
I've followed your Video. It looks like you are actually creating circular references, are you not?
I mean Shape.Data => Doc.Data => Shape.Data

I think it is impossible to properly calculate, so you have some random results here...
If it was one shape, Visio probably would complain about that, but since it's in a different shape probably Visio overlooks this - not sure why the "circular reference" error is not raised.

If you actually want global values, you could follow this article:
http://visualsignals.typepad.co.uk/vislog/2011/11/shapes-with-global-values.html

Basically, the idea looks elegant:
1. You create a document property "User.Data"
2. You create a shape property "Prop.Data", with formula set to "SETATREF(TheDoc!User.Data)" - this will redirect writes to TheDoc!User.Data
3. You add a shape field, and set it's formula to "TheDoc!User.Data"

You could try the sample stencil attached.
« Last Edit: August 15, 2018, 05:27:23 PM by Nikolay »

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3858
  • Ideas Visio-lized into solutions
Re: Can you clone shapes to another sheet?
« Reply #13 on: August 16, 2018, 09:22:09 AM »
Here's slightly different approach, but basically same idea.  This allows you to enter data at either the page or shape level.  All shapes will get the same data in this arrangement.  You  can drop shape on a new page, and formulas will hold.  There is a caveat though.  Each page requires a User and a Data section.  The key, to avoid circular argument issue is to use the SETATREF fcns and re-direct the value to a placeholder.

The User section contains the placeholder.  In the attached  example, there is a User.R1 in the Page's shapesheet.  The Data section is for entering data.  In this example, Prop.R1.  The Prop.R1.Value cell has the formula:  =SETATREF(User.R1,SETATREFEVAL(SETATREFEXPR())).  Initially, there is no value for the setatrefexpr.  It will get this via the Page>Data entry when you assign some data to the page.

For the shape, it's a bit easier, as there is only a Data entry needed, no user section.  In the example, it's, Prop.R1.  The formula is =SETATREF(ThePage!User.R1,SETATREFEVAL(SETATREFEXPR())).  That's it.  Then, last piece, insert>field>shapedata.  This displays whatever is entered either via the page or via the shape.  Whichever was last accessed.  Both send the info to the Page's User.R1.

Now, concerning your loooooonnnnngg formula.  Yikes!  Very difficult to create in first place.  More difficult to trouble shoot.  Very difficult to follow and understand.  A year from now, it will be impossible to remember what you did / modify.  I suggest, breaking the formula into sections.  Each if section could be a separate entry either in User or Scratchpad section.  So, the 1st IF would be, say, User.IF1.  Based upon results, it could point to either User.IF2 or User.IF3.  Each User entry is simple.  The test is easy to edit, and the path is easy to follow.  My recommendation.

Finally, for more SETATREF info and lot's of examples, see http://visguy.com/vgforum/index.php?topic=6383.msg26747#msg26747

Wapperdude
« Last Edit: August 24, 2018, 11:02:41 AM by wapperdude »
Visio 2019 Pro

Requin

  • Jr. Member
  • **
  • Posts: 10
Re: Can you clone shapes to another sheet?
« Reply #14 on: August 24, 2018, 09:05:07 AM »
@Surrogate - I think your solution is the best for the situation.  I'll have to add it to the program and test it out.

@Nikolay - wasn't my video just one I found on youtube for how to clone shapes. 

@wapperdude - SETATREF is confusion indeed.  I'll have to dive into it if I don't get it working with surrogates method.  Also that formula is the shortest one.  There are much longer ones.  They are all generated by a macro though so the user will never have to deal with them.  In the macro it is broken up into parts so changing it in the future will be easy.

Edit:  Ended up just going with the easy solution of automatically putting them on a separate sheet using Surrogate's solution.  Thanks for all the help. 
« Last Edit: August 24, 2018, 01:42:42 PM by Requin »