Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Sorting Issues

Okay, have a table that is about 30 rows by about 14 columns. When I do a sort on a column most of the cells show "#ref". I know what that means but what should I consider wrt formulas when I sort. In Excel the rows get sorted by the column(s) you choose but the formulas do not change. What am I missing? Thanks in advance.


Also, is there a way to display the formulas in cells instead of the results of the formulas?

OS X Yosemite (10.10.4)

Posted on Aug 29, 2015 1:05 PM

Reply
12 replies

Aug 29, 2015 3:37 PM in response to MD_Gene

TThe most common problem with sorting is that you have "Presevered columns and Rows" that do not need to be preserved. I would go through your formulas and reevaluate wherever you see the $ sign. If it is $A1 then that preserves the column; if it is A$1 then it preserves the row; if it is $A$1 then you are preserving both the row and the column. Preserving rows tend to throw more referencing errors, because most people sort by column so it needs to be able to move the rows.


THis his is just a shot in the dark. Like Quinn said pictures and more specific questions yield more specific answers.

Aug 29, 2015 5:57 PM in response to charles.christian14

Okay, so I boiled it down to something that I hope will be a good learning lesson for me.


Here are the links showing the before, one of the misbehaving cells and the after (result after the resort).


https://www.dropbox.com/s/ywdedxs7ds7ucbv/Screenshot%202015-08-29%2020.50.20.png ?dl=0



https://www.dropbox.com/s/cj7dxjp75e20ghg/Screenshot%202015-08-29%2020.53.11.png ?dl=0


https://www.dropbox.com/s/vpojmjddd9j1pgp/Screenshot%202015-08-29%2020.54.01.png ?dl=0

Aug 29, 2015 6:33 PM in response to MD_Gene

It seems to me that you are making a budget. Have you tries the built-in template in Numbers for personal budgeting? If for some reason this template doesn't please your needs, then I have some questions regarding your table.


Where is the data coming from? If it is from another table, why not just sort the master table? Are those dates in column A? Is column B your bill that is due and the other columns how much you spent on those bills in those given months?


I am not sure why column B has an "=" this seems to be a data entry column, and they do not need to have "=" to display text. Just type the Food, not "=food".

Aug 29, 2015 6:35 PM in response to MD_Gene

Gene,


you can post directly to the forum by clicking the camera in the toolbar and selecting the same files you moved to drop box.


I did a quick look and I'm going to state the obvious so you know there is a term (or technique).... you did not use sort-safe formula..... said another way you are seeing the symptoms of formulas that will not survive a sort.


This can be solved


What are the values in column A? are they an id, and index, an entry number?


If so they are most like the key to the data.


Please post a screenshot of "Table 1" on sheet "Cost_Types"

Aug 30, 2015 7:13 AM in response to Wayne Contello

Great insight! This solved my problem. I had formulas that didn't need to be formulas. This is a spreadsheet that I moved over from Excel. I do play on re engineering them by the end of the year. I use these to track expenditures. For now my workaround is to copy the table and convert the formulas to values and do the sort periodically.


Thanks.


On the "camera" idea. Again, I am new but it looks like the clicking on it allows one to attach a file. I created the screen shot with Shift-command-4 and it created the picture in the dropbox. I would like a more straight forward way to post screen shots so your help is appreciated.

Aug 30, 2015 12:37 PM in response to Wayne Contello

Okay, I looked at it again and here's a subset of what I am trying to do but can't. I want to have the details tab that has expenditures by month. I tried sorting on column D and I get bad cells. Any insights are appreciated.

The detailed view tab where I can see expenses by month: User uploaded file




Now here's the most Types tab

User uploaded file

And here's what it looks like when I try to sort on column D

User uploaded file

Aug 30, 2015 1:48 PM in response to MD_Gene

Hi Gene,



1. If there is no other data in the "Cost types" table then I see no need for it. Select Columns A and B of your report table, copy and paste formula values. These values will always survive a sort. If you want to add a cost type, add it in this table. Delete the "cost types" table. When you draw data to this table refer to the values in column B.

2. Convert your row with the months in it to a header row. This will protect it from a sort.


quinn

Aug 30, 2015 1:58 PM in response to t quinn

I see your point. Cost_Types was "born" to allow me to extend and modify the expense categories in one place. I could do that it the Details tab with essentially the same effect. I will certainly re-architect my workbooks with this in mind.


But I think this sort worked in Excel. I wonder what the difference is in MS and Apple approach to spreads?

Aug 30, 2015 2:07 PM in response to MD_Gene

Hi Gene,


I don't use Excel so I can't speculate. I was under the impression that Numbers sorts where recently changed to act more like Excel's.


My main impression of the difference between Numbers and Excel is that Numbers is designed with the idea of many smaller tables where there can be many on a single sheet. I think ease of use considerations factor in in that Numbes does not have all the capabilities that Excel has.


quinn

Aug 31, 2015 12:49 PM in response to MD_Gene

Hello


A simple method to make the formula sort-safe is to use absolute row address when referencing external cell.


E.g., use this formula:


=Cost_Types::Table 1::B$5



in lieu of:


=Cost_Types::Table 1::B5




Another method is to use static index stored in the same row as the formula cell to reference external cell.


E.g., in the following tables, Table 1::A holds static indices to reference categories in CAT table.


User uploaded file



Table 1 (excerpt) A1 index A2 1 A3 2 A4 3 A5 4 A6 5 A7 6 A8 7 A9 8 A10 9 B1 category B2 =IFERROR(INDEX(CAT::B,MATCH(A2,CAT::A,0),1),INDEX(CAT::D,MATCH(A2,CAT::C,0),1)) B3 =IFERROR(INDEX(CAT::B,MATCH(A3,CAT::A,0),1),INDEX(CAT::D,MATCH(A3,CAT::C,0),1)) B4 =IFERROR(INDEX(CAT::B,MATCH(A4,CAT::A,0),1),INDEX(CAT::D,MATCH(A4,CAT::C,0),1)) B5 =IFERROR(INDEX(CAT::B,MATCH(A5,CAT::A,0),1),INDEX(CAT::D,MATCH(A5,CAT::C,0),1)) B6 =IFERROR(INDEX(CAT::B,MATCH(A6,CAT::A,0),1),INDEX(CAT::D,MATCH(A6,CAT::C,0),1)) B7 =IFERROR(INDEX(CAT::B,MATCH(A7,CAT::A,0),1),INDEX(CAT::D,MATCH(A7,CAT::C,0),1)) B8 =IFERROR(INDEX(CAT::B,MATCH(A8,CAT::A,0),1),INDEX(CAT::D,MATCH(A8,CAT::C,0),1)) B9 =IFERROR(INDEX(CAT::B,MATCH(A9,CAT::A,0),1),INDEX(CAT::D,MATCH(A9,CAT::C,0),1)) B10 =IFERROR(INDEX(CAT::B,MATCH(A10,CAT::A,0),1),INDEX(CAT::D,MATCH(A10,CAT::C,0),1)) C1 value C2 2 C3 2 C4 36 C5 0 C6 25 C7 10 C8 5 C9 84 C10 8



Notes.


Formula in Table 1::B2 can be filled down.


Table 1 can be sorted by any column(s).


Tables are built with Numbers v2.



Hope this may help,

H

Sorting Issues

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.