Re: [libreoffice-users] Sorting Called Data

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
> ='file:///C:/Users/ajax/Desktop/teamrankings/2010.ods'#$FIFAWC.B19
>
> http://nabble.documentfoundation.org/file/n3375803/before.png
> http://nabble.documentfoundation.org/file/n3375803/after.png

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: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted