Since you already have in excess of 65536 rows, can I assume that only
one (or zero) sheets will be updated?

If so, here's what I'd do:

On Sheet A, make a pivot table from the the 65536 row on Sheet 1.
Add to sheet A, below the previous pivot table, a pivot from the rows
on Sheet 2, continue down sheet A until you run out of space, I hope
thatyou don't.

Then create a table from the pivot tables. Set it several rows to the
right of the pivot tables. Use a formula to bring the data from the
pivot table to this offset table. Create all these table from the
multiple pivot tables in a contiguous range and then, create a final
pivot table off of that.

If you have trouble with the blank row headers in the pivot table, use
a formula like this: if(isblank(a1),"cell above this one",a1)

mw