Ask Your Question
0

Sorting a range that is referenced elsewhere

asked 2016-03-05 16:20:58 +0100

Al Vesper gravatar image

updated 2016-03-07 13:17:53 +0100

Alex Kemp gravatar image

On sheet1, cell Sheet1.B9 content is set to: =Sheet2.B9. At this point, I want to reorder everything on sheet2 by sorting on column C. I expected the references to sheet2 on sheet1 to change, such that if sort moves sheet2.B9 to sheet2.B7, the cell on sheet1 that previously referenced the sheet2 cell would have been updated, but this did not happen. Is what I've expected really the way it's supposed to work? Or is LO wroking wrong?

LO version 5.0.5.2.

update: I thought I had the answer from the thread "Calc sort is broken in 5.1.0", but after setting Tools/LibreOffice Calc/General "Update references when sorting range of cells", it wroks the same way as my original description.

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2016-03-05 16:37:43 +0100

m.a.riosv gravatar image

If I'm not wrong you only need to enable the option:

Menu/Tools/Options/LibreOffice calc/General - Update references when sorting range of cells.

edit flag offensive delete link more

Comments

Yes, I did that. Still doesn't work.

Al Vesper gravatar imageAl Vesper ( 2016-03-05 16:43:54 +0100 )edit

I have tested before comment. Please share a sample file where to verify the issue.

m.a.riosv gravatar imagem.a.riosv ( 2016-03-05 16:47:16 +0100 )edit

I took out a bunch of sensitive stuff from the problem file, but now the references follow the sort. I'll do some more sleuthing...

Al Vesper gravatar imageAl Vesper ( 2016-03-05 17:11:49 +0100 )edit
0

answered 2016-03-05 16:52:05 +0100

pierre-yves samyn gravatar image

updated 2016-03-05 16:52:55 +0100

Hi

I do not reproduce on windows with Version: 5.1.0.3 and ToolsLibreOffice CalcGeneralUpdate references when sorting range of cells checked.

HTH - Regards

edit flag offensive delete link more
0

answered 2016-03-05 17:19:08 +0100

Lupp gravatar image

updated 2016-03-05 17:19:58 +0100

I expected the references to sheet2 on sheet1 to change, such that if sort moves sheet2.B9 to sheet2.B7, the cell on sheet1 that previously referenced the sheet2 cell B9 would have been updated to reference Sheet2.B7 now,

Is the above quotation completed in boldface by me what you actually meant?
If so: Your expectation is one some users share and others do not. Even for a single user there may be reasons to want the references "adjusted" on sorting in one situation and not adjusted in another situation. In an extreme case we may want to make differences even concerning one specific case of sorting.

The dillema not seems solvable completely to me. There were discussions about this issue in the time of versions 4.2.8 through 4.3.4 if I remember correctly. As a result there was introduced the option 'Update references when sorting range of cells' in 'LibreOffice Calc' > 'General'. You may play with it.

Please be aware of the fact that tools to sort data primarily are meant to sort data and not to worry about their usage. I always was satisfied with references not updated. To understand my reasons you may study the attached example where on Sheet2 the range A1:A30 is containing numbers which originally were present in the same order in B1:B30. Colored in magenta there are 4 cells of Sheet1 that are containing formulae (3 cells: one array formula, the forth one a SUM). Sheet1.B9 was as you described it. Sheet2.B1:B30 was sorted after entering the formulae with the above mentioned option active. Were the references in the magenta cells adjusted? Why not? Was the reference in cell Sheet1.B9? Can we get a consistent behaviour this way? There are many additional related questions.
My summary of this: Always set the option 'Update references when sorting range of cells' INACTIVE.ask65742UpdateReferencesWhenSortingRangeOfCellsRejection001.ods

edit flag offensive delete link more

Comments

I'm not sleeping tonight until I figure this out. :) And yes to your question. I think I meant what you said you wondered about when you asked if I meant what I said.

Al Vesper gravatar imageAl Vesper ( 2016-03-05 17:58:08 +0100 )edit

Just slightly different opinions about the adequate precision in this case, possibly. We migth not get around the dodgy implications of 'updating' refrences if we do not stick to extreme precision.

Lupp gravatar imageLupp ( 2016-03-05 18:08:27 +0100 )edit
0

answered 2016-03-05 17:29:24 +0100

Al Vesper gravatar image

It amazes me what a reboot will fix. When I had the original problem, I did NOT have the "update references when sorting" option set. After changing the option, and after numerous close/open/retries, the problem persisted with the original file, but not with my test file. After a reboot of the machine, everything works as expected. Thanks for helping me puzzle through this. (I don't think I selected the "load LibreOffice at system boot" option on this machine when I installed LO)...

edit flag offensive delete link more

Comments

To also consider my answer and the example attached there may pay. The option 'Update references...' might be poisoned. In my opinion we are talking of yet another shell game.

Lupp gravatar imageLupp ( 2016-03-05 17:39:06 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-05 16:20:58 +0100

Seen: 410 times

Last updated: Mar 05 '16