Is it possible to sort this table assending and keep the name associated with the points?
In addition renumber the position according to the sort ranking.
Would it then be possible to reorder the sort every time the points change as they are read in from another table?
iMac, OS X Mavericks (10.9)
DT,
In Numbers, Sorting always keeps rows intact. Names and the values associated with them will always be in the same orientation after the sort as they were before the sort.
You can create a dynamic numbering by using the ROW() function. If you put the expression:
=ROW()-1 in all the Cells of column A starting at Row 2, your numbering will be preserved.
Automatic reordering is possible, but would take a second table and a lot of code. Best to do it manually by initiating a Sort.
Jerry
What is missing here, cell formating? After the sort why are so many "0"?
Tyron,
the formulas in column C are clearly not sort-safe. Can you post the formula you are using so we can help?
Column C is read in from another table as they change regularly.
I simply used the sort assendin/decending in the header row.
Yup. If you look at the column C after a sort you will see they are all scrambled because they did not survive the sort.
Is there not a way to accomplish this?
There is a way. Since you have not posted the specifics of your table I can only provide a general solution. You need to look up the name in column B on the other table and retrieve the points from a different column of the same row.
it would be something like:
In the table you sort
B2=vlookup(A2, <THE RANGE IN THE OTHER TABLE>, <THE NUMBER OF COLUMNS TO THE RIGHT OF THE >, 0)
select B2 and fill down as needed.
If you provide a screenshot of the other table I can provide a specific solution for you.
All the best,
Wayne
Here is a screenshot of the table where the data is coming from.
Does this screenshot correspond to the first one you posted (before sorting)?
Yes, the table with just the names in B and the Pts in C come from this table ion the screenshot
then where are you retrieving Larry's score (768) from?
Her is a better screen shot. Larrys pts comes from T3. It previously read (768) now reads (827).
OK. Try this:
In the small table (Ranking):
Add a new column (I added column B, which you can hide later).
B2=IFERROR(MATCH(A2, 2013 Pool Standings :: A, 0)+1, "")
C2=INDIRECT("'Table 2-1'::T"&B2)
select B2 and C2, then fill down
This will survive a sort
I'm going to hijack this thread for a second. I was going to make a simple example but got really strange results that I thought maybe someone could explain. Actually, I can explain it myself but it is obviously not what anyone would expect.
The first table uses simple formulas like =Table 2::B2 to get data from the second table.
After a "sort descending" on column B of Table 1 I get this mess. Column A forumulas are now both =Table 2:A3. Column B formulas are now both =Table 2:B3.
The explanation is that Table 1 Row 2 moved down one row to Row 3 so the cell references also moved down one row (to Table 2 Row 3). Conversely, Table 1 Row 3 moved up one row so its cell references moved up a row (also to Table 2 Row 3).
Getting back to your problem, if your formulas are like those in my prior post, one solution would be to change them slightly. Instead of =Table 2::B2 use Table 2::B$2 . Do that for all the cells,all the names and the numbers. It is kind of painful to do because you have to do them one at a time. But it will sort correctly.
Sorting?
