You are reading a single comment by @duncs and its replies.
Click here to read the full conversation.
-
If you select the source data and then 'Format as table' and check the headers box then you should be able to reference a column of the table by name.
E.g. if the table was called Table1 and you had firstCol, secondCol, thirdCol you could
=SUM(Table1[secondCol])And I think the Pivot table then references it by table name and column rather than some absolute position, so when the table grows automatically you should be good.
duncs
I seem to remember there was a way to do this without macros.
I have a set of data which I do a pivot table on. I don't want to select the whole column as then the fields default to count rather than sum and I get blanks that need to be filtered out. Plus the headers aren't necessarily in Row 1.
Data is being added at the bottom of the data set. I thought there was a way to get the pivot table to update to add that new data (rather than manually redoing the data source each time) when refreshed but I can't seem to find it.
I know I can do it via a macro, I just have memories that there was an easy way to do it.