I think I understand what you're asking, and this answer is a little long-winded, so bear with me here...
It probably helps a little to delve into how Numbers handles cell references.
Let's say the value you're referencing is in cell A1 of the table called 'Table 1' on Sheet 1.
Other cells in this table can reference this value via:
=A1
This means, literally, the cell A1 in the current table on the current sheet.
If you have another table, 'Table 2' on the same sheet, and you want to reference a cell in that table, you do so via prepending the table name, so:
=Table 1::A1
means cell A1 on the table called 'Table 1' on this sheet.
If there is no 'Table 1' on this sheet, then Numbers will look to other sheets for a table called 'Table 1'
If you have multiple sheets (e.g. 'Sheet 2' and 'Sheet 3'), each with a table called 'Table 1') then you need to prepend the sheet name:
=Sheet 2::Table 1::A1
so that Numbers knows which table you're referring to (the one on 'Sheet 2' in this case).
So what does that mean when you want to move the data around? It depends on the relationship between the cell with the formula, and the cell you're referencing.
If the source and target cells are in the same table, then you cannot easily move that value out to another table. As you've seen, cutting and pasting a cell value severs the reference.
However, if the source and target are in different tables, then you can cut one table from one sheet and paste it onto another sheet. Numbers will update all the links to include the Sheet::Table:: references as needed.
This is the key to the solution.
As you've seen, you can drag a cell from one location to another, and Numbers will update the links (e.g. drag A1 to A2 and all the references to A1 will update to point to A2).
What you might not have noticed is that you can also drag a cell between two tables on the same sheet... you can drag Table 1::A1 to Table 2::A2, and all the references will update.
You cannot, however, drag a cell (or a table) from one sheet to another..
However, mixed in all of the above is the answer to your question... put another way:
You can:
- Create a new table on Sheet 1 - call it 'Temporary'
- DRAG the target cell from your existing table to this Temporary Table -- all cell references will update to point to Temporary::A1
- CUT the Temporary Table - this will cause a number of cells to throw 'Invalid reference' errors, but that's OK for now.
- Immediately switch to a different Sheet
- PASTE the Temporary Table - all references to the cell in the Temporary Table will update to point to the pasted table, and the invalid references should correct themselves.
If you want, you can then DRAG the target cell to another table on the same sheet as the 'Temporary' table, or leave it where it is.
It's a little cumbersome, and not entirely intuitive, but hopefully it will make sense when you try it.
