On the CropAvgByZone tab, the table on the left is a Pivot table which averages the pH and EC readings by crop and zone. Table to the right shows the detailed crop readings for the specified zone and automatically updates based on the pivot table filter selections.
I have two questions:
1) Is there a way to make the table on the right automatically resize to fit the data in that table? So that, if there are only 3 data points, the table will only have 3 lines of data, and if there are 25 or 50 data points, the table resizes to fit those exactly? I currently have the table sized with a lot of extra rows because otherwise, if there is more data than rows, some data gets cut off. The number of data points varies from week to week, so ideally this will display all of the data even if there are 2 entries or 2000 entries.
** Regarding #1, in AutoFormat both boxes are already checked for “Include new rows and columns in table” and “Fill formulas in tables to create calculated columns”.
2) If I filter the table on the right to show just one crop, it sometimes hides rows from the Pivot Table. I would like the full pivot table to remain stationary and visible regardless of what is happening in the right table. Is there a way to keep the pivot table from changing? Should I just move the table on the right below the pivot table? I just prefer the higher placement because it's easier for the team to more readily see.
I am working in Excel for Microsoft Office 365.
Please let me know if you have any questions. Thanks!
Bookmarks