Data source for pivot table based on contents of cell

**FernTurpin** · 06-23-2015, 11:25 AM

Hi all, I have a series of pivot tables (18) that I use weekly.

I am constantly updating the Data Source on all 18 pivot tables every week.

Is it possible to link the data source of the pivot table to the CONTENTS of a cell, so this way I can adjust 1 cell, and then all the pivot tables will then have their data source updated?

Thanks

**seasider89** · 06-23-2015, 11:32 AM

This link may help.

**FernTurpin** · 06-23-2015, 11:33 AM

Thanks... I think that's just the piece I needed

**nigelbloomy** · 06-23-2015, 11:39 AM

If you go to the name manager you can link your pivot tables to the named range instead of the actual cells. You can use a formula like this to create your named range that will update the size when new data is added.

Please Login or Register  to view this content.

This looks at Sheet1 and the data that starts in A1. Then it creates a range that is as tall as the data in column A and as wide as the data in row 1. This way when any new information is added to the rows or the columns, the pivot table will see it when you hit refresh.

This may help as well: http://www.contextures.com/xlNames01.html