Can you clone shapes to another sheet?

Started by Requin, August 14, 2018, 03:26:30 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Requin

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
=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)
=IF(FORMULAEXISTS(REF())=FALSE,TRUE,REF()&CHAR(10))&"FC"&REF()&","&REF()&"-"&REF()&CHAR(10)&REF()&" "&REF()&"/"&IF(REF()=1,"BUR","AER")

Surrogate

#1
Of course, if on another page (that you mean as sheet) not exist shape with name "Fibre cable" you get REF() in your formula

Paul Herber

... so if you copy the shape "Fibre cable" first ...
Electronic and Electrical engineering, business and software stencils for Visio -

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

Requin

Quote from: Paul Herber on August 14, 2018, 04:20:14 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

#4
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
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 ?

Paul Herber

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 for Visio -

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

Requin

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

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

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
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

@Surrogate, clever to keep the reference on the orginal page 8).
Yacine

Requin

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

#11
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
=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

Nikolay

#12
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.

wapperdude

#13
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
Visio 2019 Pro

Requin

#14
@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.