Date: prev next · Thread: first prev next last
2011 Archives by date, by thread · List index

At 04:59 28/09/2011 -0700, Nobody Noname wrote:
When I go to sort data from a column that contains data from other spreadsheets, it messes up my equations and called cells. How do I get these to stay in place while sorting?

I'll use Tunisia as an example... before the sort its cell is defined as
='file:///C:/Users/ajax/Desktop/teamrankings/2010.ods'#$FIBAWC.B24 and then after I sort it its all of a sudden: ='file:///C:/Users/ajax/Desktop/teamrankings/2010.ods'#$FIBAWC.B#REF! (its trying to call a cell that is not there in another spreadsheet). Why is it changing my formula when I was it to be 2010.ods'#$FIBAWC.B24?

Another example is Italy, I want it and have it to be
='file:///C:/Users/ajax/Desktop/teamrankings/2010.ods'#$FIFAWC.B26 and then after I sort it goes to

When you sort formulae, the sorting is performed on the results of the formulae - the numbers or text values (or whatever) that you see. But sorting consists of moving data from cell to cell, and - just as when you copy and paste formulae - the row and column references in formulae are modified according to the displacement that occurs.

It's not entirely clear from your "after" picture which rows the data ends up in, but it appears from the formulae you quote that the "after" picture starts in row 1, not row 16, like the "before" picture. This means that Italy, which starts in row 21, ends up in row 14 - seven rows higher - so the "B26" in its formula is properly converted to "B19". This no longer refers to Italy's data in the source file, of course - which is your problem.

By the same token, Tunisia starts in row 43 and ends up in row 1 - forty-two rows higher. The "B24" in its formula would need to be converted to "B-18", suggesting a non-existent row off the top of the sheet, so it appears as "B#REF!".

The solution, which has already been suggested, is to make your row references absolute: "B$26" and "B$24" would not be modified in the way you notice. You can make this change to your formulae easily using Find & Replace if you select Formulas for "Search in" (in More Options).

I trust this helps.

Brian Barker

For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.