Sorting Called Data

users
#1

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

#2

Spreadsheets are about numbers. You've got to sort numbers rather than text.

#3

But shouldn't it sort the result of the equation? I thought that was
standard in spreadsheets.

#4

Am 28.09.2011 15:12, mcbad wrote:

But shouldn't it sort the result of the equation? I thought that was
standard in spreadsheets.

--
View this message in context: http://nabble.documentfoundation.org/Sorting-Called-Data-tp3375803p3376004.html
Sent from the Users mailing list archive at Nabble.com.

Yes, of course. But the resulting cell value is NOT the displayed text.

#5

I'm not sure what you mean. I did this same exact thing on Google Docs and
it worked fine, it sorted by the resulted number from the requested
spreadsheet and cell number. Does this mean I cannot call data from another
spreadsheet and sort it?

#6

try using absolute references like

#$FIBAWC.$B$24

L.

#7

Am 28.09.2011 16:08, mcbad wrote:

I'm not sure what you mean. I did this same exact thing on Google Docs and
it worked fine, it sorted by the resulted number from the requested
spreadsheet and cell number. Does this mean I cannot call data from another
spreadsheet and sort it?

--
View this message in context: http://nabble.documentfoundation.org/Sorting-Called-Data-tp3375803p3376139.html
Sent from the Users mailing list archive at Nabble.com.

Sorry, my answer was wrong. The #REF has nothing to do with sorting.

In Excel, Gnumeric and Calc you get #REF errors when
1) referring to the second sheet $Sheet2 absolutely and copy the reference to another document which has no second sheet.
1b) referring to the previous sheet Sheet2 relatively and copy the reference to the first sheet. Then there is no previous sheet anymore.
2) copying/moving a relative reference =A1 one row up since there is no row before row #1
3) copying/moving a relative reference =A1 one column to the left since there is no column before column A.

#8

Am 28.09.2011 16:08, mcbad wrote:

I'm not sure what you mean. I did this same exact thing on Google Docs and
it worked fine, it sorted by the resulted number from the requested
spreadsheet and cell number. Does this mean I cannot call data from another
spreadsheet and sort it?

--
View this message in context: http://nabble.documentfoundation.org/Sorting-Called-Data-tp3375803p3376139.html
Sent from the Users mailing list archive at Nabble.com.

Sorry, my answer was wrong. The #REF has nothing to do with sorting.

In Excel, Gnumeric and Calc you get #REF errors when
1) referring to the second sheet $Sheet2 absolutely and copy the reference to another document which has no second sheet.
1b) referring to the previous sheet Sheet2 relatively and copy the reference to the first sheet. Then there is no previous sheet anymore.
2) copying/moving a relative reference =A1 one row up since there is no row before row #1
3) copying/moving a relative reference =A1 one column to the left since there is no column before column A.

#9

Am 28.09.2011 15:12, mcbad wrote:

But shouldn't it sort the result of the equation? I thought that was
standard in spreadsheets.

--
View this message in context: http://nabble.documentfoundation.org/Sorting-Called-Data-tp3375803p3376004.html
Sent from the Users mailing list archive at Nabble.com.

Yes, of course. But the resulting cell value is NOT the displayed text.

#10

Spreadsheets are about numbers. You've got to sort numbers rather than text.

#11

Hi :slight_smile:
Text is numbers in this space-time continuum.  Surely text gets sorted by it's ascii code or something?  I'm not sure how equations get sorted tho.
Regards from
Tom :slight_smile:

#12

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

#13

Thanks, this works.