Numbers: why are references to a pivot table preserved (i.e., $)?

Question

I have a pivot table on sheet 1 and try to make another table with data from the pivot table on sheet 2 (because you can't add columns to a pivot table, can you?). However, if I write a formula like this

Table 1 Pivot::$C$3÷Table 1 Pivot::$B$3−1

as you can see all references are preserved ($). If I manually delete the $, Numbers reinsert them when I confirm the formula.

Why is this? How can I get around it?

Answer 1

Not sure if there is a direct solution. If you wish to be able to make use of the values in a pivot table in a formula with non-absolute referencing, you can use the following workaround:

1. Select the whole pivot table by clicking on the top left corner or by selecting any cell and then pressing Command+A.
2. Right-click and choose Copy Snapshot.
3. Go to any empty part of your spreadsheet, right-click and choose Paste.

This will create a values-only replica of the pivot table and you can refer to any of the cells in this table with non-absolute referencing in a formula. I reckon this is not a great workaround because the replica won’t refresh if and when necessary, but it appears that this is the only one possible at this time.