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

Thanks, this works.

On Wed, Sep 28, 2011 at 11:43 PM, Brian Barker [via Document Foundation Mail
Archive] <> wrote:

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
and then after I sort it its all of a sudden:
(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

Another example is Italy, I want it and have it to be
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

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

I trust this helps.

Brian Barker

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

 If you reply to this email, your message will be added to the discussion
 To unsubscribe from Sorting Called Data, click 

View this message in context:
Sent from the Users mailing list archive at
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.