Formulas in a new table that refer to cells in a pivot table — can they be relative references?
I've been doing a lot of experimentation with Numbers' pivot tables and there's one thing (so far) I have not been able to figure out that I'm hoping someone can answer.
I often want to create a separate table with formulas based on the pivot table values. However, I usually do not want the cells in the new table to be absolute references to pivot table cells, so I can easily copy a formula in the new table from one cell to another. This can easily be done in Google sheets.
Numbers makes formulas absolute references by default, when pointing to cells in a pivot table. When I manually remove the $ in the cell formula to change the cell reference to relative from absolute, Numbers refuses to accept the edit and continues to leave it as an absolute reference.
As an example, here's the text of a formula I create in the new table. I can't remove the $. Therefore I can't copy this formula to other cells in the new table since everything will still point to the original pivot table cells.
$C$3÷$B$3, where C and B are pivot table cells.
Is there a solution? If not, creating formulas in a separate table, is very laborious.