[Solved] Drawing lines automatically between items/cells

[mmber]

Not sure if anyone can help.

I'm trying to get automatic lines drawn between two identical lists that have been sorted differently. The lines will show where the items have been ranked in the second list, if they still group together etc.

I thought glue points could do this but found that these are only present in draw. If i put the data in draw as a spreadsheet I can't use glue points on the cells. i could be on a totally wrong track here...if anyone has any ideas....

[Hagar Delest]

In Draw, have you tried to use the Connectors toolbar (switch is in the Drawing toolbar) to do that? Use text boxes and create such a connector between them.

Thanks to add '[Solved]' in your first post title (edit button) if your issue has been fixed.

[acknak]

I can't think of any way to have the lines stay connected to the items as they move around. Calc or Writer can sort the items but they don't support connectors. Draw supports connectors but can't sort the items.

If you really have to have the lines drawn automatically, you'll need a macro program: either to draw the lines in Writer, or to sort the items in Draw.

[huw]

You can do it with the Detective.

Instead of using identical lists, make the second list consist of absolute cell references to the first. If this isn't practical then you could add helper columns to both lists, one of which refers to the other using absolute cell references.

Then use the detective to trace precedents for the second list. You'll have to do it cell by cell unfortunately, but only once.

Now when you sort one list, the traces move as you require.

In the attached screen grab I've right aligned the second list, and turned on Display Formulas, so you can see what I mean. Then I sorted column B only, descending.

[foxcole]

Calc or Writer can sort the items but they don't support connectors.

Writer supports connectors, it just doesn't have a connector tool. You have to go into Draw and drop a connector (or connectors) into the Gallery and use that in Writer.

But that's a moot point here, of course, because connectors only work on graphic objects. You can't glue them to paragraphs. I think huw's solution is brilliant... I never realized that function existed.

The only problem I've found is that I can't seem to find a way to change the arrow position. The arrow in my example starts at what amounts to a .25 inch indent, so it's in the middle of the text on the precedent side and ends on the dependent side an equal distance into the cell. I'm thinking that for anything except very short text, that might not be desirable.

[acknak]

Yes, I agree: Nice idea huwg!

And you have a good point, Fox.

Hmm. That gives me an idea though: what happens if you pop a connector into a Writer doc, and connect it to graphics that are anchored "As character"?

This:

sort_connect.png (4.78 KiB) Viewed 18980 times

Ha! Not bad.

It's definitely a little weird though. The connector has to be anchored "To page" and it still tends to hop around for no apparent reason sometimes, but it might be worth a try.

One bog drawback: It's very hard to order the items by hand. You're pretty much stuck with ascending/descending sort.

[foxcole]

Hmm. That gives me an idea though: what happens if you pop a connector into a Writer doc, and connect it to graphics that are anchored "As character"?

Nice one!! Well done.

The connector has to be anchored "To page"

Why?

One bog drawback: It's very hard to order the items by hand. You're pretty much stuck with ascending/descending sort.

Could you add an extra column temporarily, and type numbers into it in the order you want the second column to sort? (So, for example, if you want item 3 to be in item 5's position after reordering, you'd type a 5 in that column for item 3.)

[acknak]

Why?

I don't know. Because if you don't, then it doesn't work.

The default is "Anchor to paragraph", in which mode Writer always wants to move the anchor to the closest paragraph, but in doing so, it seems to also move the connector. What happens is, you move the connector ends to link up with the graphic, and as soon as you release the mouse, the connector jumps to a different position. When the anchor is "to page", the connector stays where you put it.

I also changed the connectors to "Wrap through" just to avoid any problems, but that doesn't seem to matter at all.

Could you add an extra column temporarily, and type numbers into it in the order you want the second column to sort?

Sure, that works fine.

Here's the file (including your suggestion) if anyone wants to try it:

[huw]

I think huw's solution is brilliant... I never realized that function existed.

The only problem I've found is that I can't seem to find a way to change the arrow position. The arrow in my example starts at what amounts to a .25 inch indent, so it's in the middle of the text on the precedent side and ends on the dependent side an equal distance into the cell. I'm thinking that for anything except very short text, that might not be desirable.

That's why I suggested helper columns. Here's an example of them in use.

trace hidden columns.PNG (5.75 KiB) Viewed 18945 times

Traces somehow feel fragile though, despite seeming to be robust in practice.

I like acknak's idea. Proper connectors strikes me as more elegant, and you seem to have anchoring figured out. They certainly look nicer than Calc traces. I do see what you mean about rearranging by hand though, - that's possible in Calc, but doesn't seem to be in Writer.

I'd say they're both workable hacks, each with pros and cons. Which you use is down to personal preference, and which application you're familiar with.

[mmber]

All brilliant ideas

I've found the trace detective in calc works well apart from having to individually tell each cell the absolute cell reference-would be good if it could just be copied down. same with the trace decendants-only works for one cell at a time.

I tried it with the connectors in word and it works for small lists but crashes when I do my own list of some 150 odd items.

Also, when I tried to copy the connector and points down the table in helper columns (to make things a little faster) I noticed the point graphics paste ok but the connector lines don't paste through if they are in a group with the graphics (they do copy/paste if done only as a connector but then have to join each connector by hand each time).

Personally, I think the connectors look more elegant than the trace but that the trace works better.

[acknak]

I think you came to the right conclusion. I'm glad we were able to find something you could use after all.

[huw]

I've found the trace detective in calc works well apart from having to individually tell each cell the absolute cell reference-would be good if it could just be copied down.

To speed this up, do the first one as a relative reference, drag-fill down so they are all relative, then use Shift+F4 to make them absolute (note it cycles through the four different types of referencing, so be sure to press it only once for each cell).

If you are regularly doing this for 150-item lists I would record a macro of Shift+F4 then cursor down, or even better, make a template, with the lists empty and helper columns filled in ready to go.

[acknak]

Find & Replace works too.

You need two passes with the current release; the upcoming release will do it in one pass.