0

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?

1 Answer 1

0

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.

2
  • That is what I did as a workaround. Is there some intrinsic reason why references to a pivot table are limited like this?
    – d-b
    Commented Nov 21, 2023 at 10:38
  • @d-b Sorry, I don't know if there is any such reason. The interesting thing is that if you rearrange the pivot table, Numbers changes the address in the formula if necessary, trying to have the formula point to the same data in the pivot table. If you haven't already done so, you may consider giving Apple some feedback on this issue if this is something you really need.
    – Alper
    Commented Nov 22, 2023 at 9:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .