Referencing to cells problem

"When I put the cel A2 in the left table (the cel containing "Charlotte") to the top, the reference in the right table changes together with the cel, which means nothing changes in the right table, as seen on the picture below."

Instead of moving the cell, move the data. Numbers formulas track and adjust for changes in the location of their referenced cells.

If you add a header row to the table on the left, you could use OFFSET to allow your formula to always reference cell A1 (in the header row, which does not move, even in a sort of the column), then retrieve the value from a cell a specific number of cells below A1.

Using your example, with a header row added, your formula in Opdrachten::A1 would be:

="Laat "&OFFSET(Namen:$A$1,2,0)&" met een kegel…30 seconden")

Moving the cell containing Charlotte to a position above the one containing Arno won't change the cell reference, and A1 has not moved, and the 2 row offset will still point to the cell two rows below A1.

Here's my sample:

Ignore the difference in Table names in the two formulas. Table 1-1 is a duplicate of Table 1, made so I could show both stages, before and after moving Charlotte's cell. The upper formula is from the original (taken before switching positions of the two cells, the lower on is from Table 1-1, the duplicate (made before switching the cells), and was taken after switching the cell positions in that table.

I dropped the PLTTE.TEKST (PLAINTEXT) function from the formula as it seemed unnecessary. PLAINTEXT removes any formatting from the text it is applied to. Placed as shown in your initial formula, PLAINTEXT applies only to the text written into the formula itself, which does not appear to have any formatting applied.

Regards,

Barry

And you have another, potentially simpler, option. Just use INDEX function with the row number as the second parameter. For example if you want to refer to row 2 in the names table, you could do this:

The formula:

=INDEX(Namen::A,2)

When the order changes in column A of the Namen table, the reference to the second row remains intact:

Potential advances over OFFSET:

• More compact. Two parameters instead of three.
• The second parameter can be easier to understand: 1 is row 1, 2 is row 2, etc.
• Doesn't require adding a Header Row if you don't already have one.
• OFFSET is a so-called "volatile" formula, which recalculates every time you make a change anywhere in your document. If you have a lot of formulas with OFFSET in a large document then your document could become sluggish.

Substitute ; for , in the formula if your machine is set to a region that uses , as a decimal separator.

SG

INDIRECT also locks cell references in place, if your goals to allow yourself to continue with switching cells rather than the cells' contents.