Paste pivot table values to another sheet with relative formulas

Question

Level 1

3 points

Paste pivot table values to another sheet with relative formulas

I understand I cannot add additional rows or columns to a pivot table to do additional calculations. So I'd like create to copy linked to the original pivot table.

The linked table cannot be built with relative references. Example: If I say "Table 1 Pivot::$A$2", and edit the formula to say "Table 1 Pivot::A2", Numbers puts the "$" back into the formula, so I cannot simply copy and paste the formula to create a linked table back to my original pivot table.

Any ideas?

Posted on Oct 15, 2022 6:41 AM

Reply

Answer 1

Ralf-F

Level 4

2,711 points

Oct 15, 2022 10:52 PM in response to sbennett3705

You could try this.

Option A, will work if you don't add rows / columns between the existing rows / columns.

A1= INDEX(Table 1 Pivot::$A$1:$F$7,ROW(cell),COLUMN(cell),area-index)

It will get the row / column information from its position in the table.

Option B, will also work if you add rows / columns between the existing rows / columns.

You would define the row column position in an additional row / column.

B2= INDEX(Table 1 Pivot::$A$1:$F$7,$A2,B$1,area-index)

You must preserve column A and row 1, to drag the formula to the rest of the table

Should work as long as the Pivot table has always the same layout / no new rows / columns are added.

Every empty cell in Pivot table will return a 0, you would have to delete the formula from these cells.

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Regards Ralf

Reply

Answer 2

sbennett3705 Author

Level 1

3 points

Oct 16, 2022 4:55 AM in response to Ralf-F

Thanks Ralf. I'm an Office user trying to star using the iWork apps and find these sorts of issues puzzling.

Your suggested formulas produce syntax errors?

The formula below produces the right result but cannot be copied and pasted to further cells:

So, I modified it to read the row number which should work, but again it produces an error, even though Row(C2) produces the correct row number (2) when used by itself.

Puzzling indeed....

Reply

Answer 3

sbennett3705 Author

Level 1

3 points

Oct 16, 2022 9:41 AM in response to Ralf-F

Here's the worksheet.

C2 should index to the first row of the pivot table to the left. It only works because I used this formula instead of the one you suggested. but this is hard coded to "1,1" which is not achieving anything:

What am I missing?

By the way, in Excel it's simply: =INDEX(A1:A4,ROW(),1). This formula copies to the next cell and automatically indexes based on the row number. How is this done in Numbers?...

Reply