[Solved] Drawing lines automatically between items/cells

Discuss the spreadsheet application

[Solved] Drawing lines automatically between items/cells

Postby mmber » Wed Jan 23, 2008 7:54 am

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....
mmber
 
Posts: 2
Joined: Wed Jan 23, 2008 7:33 am

Re: drawing lines automatically between items/cells

Postby Hagar Delest » Wed Jan 23, 2008 9:08 am

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.
LibreOffice 6.4.6 on Xubuntu 20.04 and Windows 10.
User avatar
Hagar Delest
Moderator
 
Posts: 29084
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: drawing lines automatically between items/cells

Postby acknak » Wed Jan 23, 2008 3:53 pm

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: drawing lines automatically between items/cells

Postby huw » Wed Jan 23, 2008 4:21 pm

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.
Attachments
traced sort.PNG
traced sort.PNG (9.56 KiB) Viewed 16659 times
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: drawing lines automatically between items/cells

Postby foxcole » Wed Jan 23, 2008 9:27 pm

acknak wrote: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.
Attachments
Calc Detective sample.png
Calc Detective sample.png (2.1 KiB) Viewed 16649 times
Cheers!
---Fox

OOo 3.2.0 Portable, Windows 7 Home Premium 64-bit
User avatar
foxcole
Volunteer
 
Posts: 1507
Joined: Mon Oct 08, 2007 1:31 am
Location: Minneapolis, Minnesota

Re: drawing lines automatically between items/cells

Postby acknak » Wed Jan 23, 2008 10:29 pm

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
sort_connect.png (4.78 KiB) Viewed 16641 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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: drawing lines automatically between items/cells

Postby foxcole » Wed Jan 23, 2008 10:56 pm

acknak wrote: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.

acknak wrote: The connector has to be anchored "To page"

Why?

acknak wrote: 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.)
Cheers!
---Fox

OOo 3.2.0 Portable, Windows 7 Home Premium 64-bit
User avatar
foxcole
Volunteer
 
Posts: 1507
Joined: Mon Oct 08, 2007 1:31 am
Location: Minneapolis, Minnesota

Re: drawing lines automatically between items/cells

Postby acknak » Thu Jan 24, 2008 12:05 am

foxcole wrote: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.

foxcole wrote: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:
Attachments
Sort_connect.odt
(9.12 KiB) Downloaded 317 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: drawing lines automatically between items/cells

Postby huw » Thu Jan 24, 2008 12:44 pm

foxcole wrote: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
trace hidden columns.PNG (5.75 KiB) Viewed 16606 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.
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: drawing lines automatically between items/cells

Postby mmber » Fri Jan 25, 2008 1:19 am

All brilliant ideas :D

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.
mmber
 
Posts: 2
Joined: Wed Jan 23, 2008 7:33 am

Re: drawing lines automatically between items/cells

Postby acknak » Fri Jan 25, 2008 2:44 am

I think you came to the right conclusion. I'm glad we were able to find something you could use after all.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: drawing lines automatically between items/cells

Postby huw » Fri Jan 25, 2008 11:32 am

mmber wrote: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.
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: drawing lines automatically between items/cells

Postby acknak » Fri Jan 25, 2008 4:01 pm

Find & Replace works too.

You need two passes with the current release; the upcoming release will do it in one pass.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests