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.
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