Relative cell reference (vs absolute) isn't preserved

I guess I should have picked up on that from the table names. It appears you cannot change it when referencing a pivot table. I'm sure there is a reason for that. They are not normal tables. You likely can still get the results you want by using OFFSET along with COLUMN and ROW and some math. I think I'd be careful referencing cells in a pivot table, though, just like I would with a categorized table. Things may move around.

Formula in Table 1::B2 =OFFSET(Sales Pivot::$C$3,ROW()−2,COLUMN()−2)

Fill right and down.

Another formula would be INDEX

B2 =INDEX(Sales Pivot::$C$3:$E$5,ROW()−1,COLUMN()−1)

Another option would be lookup functions.

What you said you are doing should have worked. Unless you are accidentally clicking the X, I don't know what is going on. Try hitting Return instead of clicking the checkmark and see if that does it.

glashoppah wrote:

I wish the pivot table weren't "special". Once it's built, it should behave like a regular table, allowing modification, etc.

Pivot Tables in Numbers, as in Excel, are mainly used for flexible analysis on the fly, not for presentation.

You can use them to calculate and arrange summaries the way you need. Then, if you want, just copy paste the results into a "regular" table for dressing up or further analysis.

They have their quirks, but they are tremendous time savers.

SG

glashoppah wrote:

I've been using them for over 30 years, starting way back before excel auto-generated them, back when they were called "crosstabs". I just think that once the resulting table has been generated, it should behave just like any other table.

My experience is different. I don't think Crosstabs were the same thing as the Pivot Tables introduced to Excel in the 1990s. Pivot Tables have never behaved like "regular" tables there. In fact Excel didn't even have tables until relatively recently. The Excel design, as you know, is centered around one big expanse of cells on each sheet. The introduction of tables to Excel brought its own addressing quirks. Numbers tables tend to be much easier to use.

I think it is a good thing in Numbers (and Excel) that Pivot Tables don't behave like "regular" tables. You want to be able to switch things around quickly and try different things. Pivot Tables are ideal for that kind of dynamic analysis.

To a limited extent you can indeed address cells within a Pivot Table, both in Excel and in Numbers. But, because Pivot Tables are intended to be dynamic, the addressing easily breaks down.

Far better, if your project requires a "fixed" analysis and presentation stage that a Pivot Table can't handle, to simply copy-paste data out of the Pivot Table into a "regular" table and work with it there. Easy to do, and more reliable.

SG

Your memory is correct. From what little I have used Excel pivot tables, they are incorporated into the worksheet and you can reference the cells just like you would any other cells.