Batch or Shortcut to preserve the original row or column of cell references in Numbers 3.5

Derick,

It seems that the programmers didn't account for upward compatibility when they changed the sort behavior in Numbers V3. I think we would all be better off if they hadn't touched it, but new users who were coming from the Excel world apparently complained enough to get it changed over the the Excel way. Or, maybe it was for compatibility with Excel in the iCloud version. In any case they didn't get it right for the loyal users of earlier versions of Numbers.

I'd be happy to take a look at your expressions and see what might be done to ease the pain. How about either posting an example, in context, for us to look at? If you wish, you may send me a sample file. Generally you only need to edit the first instance of an expression in a column and Copy/Paste to the rest of the rows.Let's see what you have and go from there.

Jerry

Hello

I cannot open your Numbers v3 file because I'm using Numbers v2. The following is based upon your screenshots.

As far as I can tell, the cause of mess in references after sort in Numbers v3 would be your use of absolute row addressing in referencing cell in Activity, Month and Notes columns.

A remedy would be to change absolute row addressing to relative row addressing in the relevant columns, such as:

$Activity $X => $Activity X $Month $X => $Month X $Notes $X => $Notes X

By the way, it is not good idea to use header cell names as references especially when debugging tables because it makes it very difficult, if not impossible, to recognise addresses.

Also I wonder how you have input those formulae with absolute references even when the referent address is to be relative to the formula adddress. Absolute reference won't change in fill down/right/up/left operations. Perhaps you have input it one by one in each cell? If you have to input similar formula one by one in each cell, you'd almost always better reconsider formula and/or table structure so that you can fill it down/right/up/left across target range because it is the designed way how spreadsheet formulae are input across a large number of cells.

For instance, I'd guess the following absolute row addressings are also to be relative row addressings in order for the containing formula can be filled down/up (although these references seem to be in different table(s) and thus not affected by sort operation in current case).

$Pay Status $X => $Pay Status X $Day Due $X => $Day Due X

* I presumed all rows referenced by X above are rows corresponding (relative) to the row where the formula resides. If this presumption is correct, you may fill down/up the modified formula whose absolute rows are replaced with relative rows as explained above across column E, which should be sort-safe in Numbers v3 if I'm not mistaken.

Good luck,

H

Oops. Of course I'm mistaken 😟

The last two references may not be relative row references because they are not in the same table (precisely, not in the range whose rows are moved by sort operation).

So the following modicication would be WRONG.

$Pay Status $X => $Pay Status X $Day Due $X => $Day Due X

You'd need to keep them using absolute row reference although by doing so you have to change these absolute row addresses after filling down/up the formula.

You might introduce record index column and lookup other tables using the index. This way you would be able to get rid of these absolute row references which prevents simple fill operations.

Regards,

H

Hello

Couple of comments.

1) Sort behaviours on reference in formula in Numbers v2 and Numbers v3 are completely different.

2) You can use Numbers v2 under OS X 10.10. Remember to upadte to Numbers 2.3.

iWork 9.3

http://support.apple.com/kb/DL1563

3) As for record index column, I meant to say something like this.

Table 1 (excerpt) A1 n A2 1 A3 2 A4 3 B1 x B2 10 B3 3 B4 6 C1 y C2 6 C3 3 C4 1 D1 x(n)+y(n) D2 =B2+C2 D3 =B3+C3 D4 =B4+C4 E1 x(n)+y(n)+z(n) E2 =B2+C2+Table 2::B2 E3 =B3+C3+Table 2::B3 E4 =B4+C4+Table 2::B4 F1 x(n)+y(n)+z(*n) F2 =B2+C2+INDEX(Table 2::B,MATCH(A2,Table 2::A,0),1) F3 =B3+C3+INDEX(Table 2::B,MATCH(A3,Table 2::A,0),1) F4 =B4+C4+INDEX(Table 2::B,MATCH(A4,Table 2::A,0),1)

Table 2 (excerpt) A1 n A2 1 A3 2 A4 3 B1 z B2 1 B3 8 B4 4

Notes.

Table 1::A and Table 2::A are record index columns.

In Numbers v2, you may sort by any column in Table 1 and Table 2 and yet E should equate to F.

In Numbers v3, if I'm not mistaken, when you sort by any column other than A, E should not equate to F. And results in F will be what you want and expect.

Formulae in E2 and F2 can be filled down.

Tables are built in Numbers v2.

Hope this may help you to understand the issue.

H