+ Reply to Thread
Results 1 to 2 of 2

Auto resize lookup table to data points & keep pivot table as is when filtering other tble

  1. #1
    Registered User
    Join Date
    01-28-2021
    Location
    Florida
    MS-Off Ver
    Microsoft Office 365
    Posts
    18

    Auto resize lookup table to data points & keep pivot table as is when filtering other tble

    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!
    Attached Files Attached Files
    Last edited by mo1h43v; 09-14-2021 at 08:00 AM.

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,472

    Re: Auto resize lookup table to data points & keep pivot table as is when filtering other

    If you can put up the table TableCropReadings with formulas linking to PivotTable_AvgByZone, you can do the same with another PivotTable in place of the table TableCropReadings and link the 2 PivotTables via Slicers.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto Resize Table upon data entry from separate sheet
    By sorensjp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2020, 06:50 AM
  2. Power Pivot: Filtering 2 fact tables based on Lookup table
    By Willem2904 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-12-2019, 03:29 PM
  3. Replies: 5
    Last Post: 07-01-2019, 08:01 AM
  4. [SOLVED] Using pivot table to lookup data from large table, but would like to edit
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2017, 12:29 PM
  5. [SOLVED] Filtering Data Pivot Table is Based On - Impact on Pivot Table
    By bbg22 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-17-2013, 06:34 AM
  6. import data from access table to excel pivot table - Enable Auto Refresh
    By okl in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-01-2010, 09:38 AM
  7. [SOLVED] Auto-Filtering a Pivot Table?
    By No Spam in forum Excel General
    Replies: 2
    Last Post: 11-30-2005, 06:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1