[libreoffice-users] Re: Sorting Called Data

Thanks, this works.

On Wed, Sep 28, 2011 at 11:43 PM, Brian Barker [via Document Foundation Mail
Archive] <ml-node+s969070n3378285h95@n3.nabble.com> 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
> > ='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: [hidden 
> email]<http://user/SendEmail.jtp?type=node&node=3378285&i=0>
> 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
>
>
>
> ------------------------------
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://nabble.documentfoundation.org/Sorting-Called-Data-tp3375803p3378285.html
>  To unsubscribe from Sorting Called Data, click 
> here<http://nabble.documentfoundation.org/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=3375803&code=NDhtY2JhZEBnbWFpbC5jb218MzM3NTgwM3wtMTY4Mzk0NDIyNA==>.


--
View this message in context: 
http://nabble.documentfoundation.org/Sorting-Called-Data-tp3375803p3378417.html
Sent from the Users mailing list archive at Nabble.com.
-- 
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