2

I've made a test sheet here (https://docs.google.com/spreadsheets/d/17l49Rn3ZbNx-upzBtmRtsJBHQiCKXuYPs8SMTyrcw9k/edit#gid=0)

You'll notice that the pivot table IS correctly pulling in ALL the data in A:B, not just limited to the existing rows of data.

This way, as new rows are appended, the data is automatically added to the pivot.

However, I've discovered a limitation. If a new row contains a new Name value, e.g., C, then that will NOT get automatically added to the pivot, since the pivot has a filter. Even though the filter is for "All items", since C wasn't existing at the time at which the filter was added, once I add a row C, I have to manually adjust the filter (re-select all) to pull it into the pivot.

This is not desired behavior for me, I would like the pivot to not be constrained (i.e., if it previously was set to select all, then continue selecting all) UNLESS I had said only A, then don't give me C even if C is appended!

Is this possible?

Or if not, at the very least, is it possible to print out the filter somewhere formulaically? (in a cell, =Filter(pivot)), this way at least I have a warning/reminder?

Other solutions to deal with this welcome!

2
  • I don't know if this can be solved in pivot tables. My answer below simply offers an alternative that dynamically updates. Pending a pivot table-based solution I think that's better than implementing a warning/reminder.
    – Blindspots
    Nov 29, 2023 at 19:42
  • Ah so... unfortunately the pivot table in actuality is quite complex, and I really need to use it. HOWEVER I do think the Filter can be a good way of highlighting missing names!
    – james
    Nov 29, 2023 at 22:04

1 Answer 1

0

Alternative Using QUERY

For what you are doing you could use QUERY Function instead which would update dynamically. I also included at the end a typical formula that returns items from one list that are not found in another.

Grand Total at Bottom

You could add conditional formatting (font and fill, but not borders) if you want to make the Grand Total stand out.

={ QUERY(A:B, "Select A, SUM(B) Where A is not Null
     Group by A Label SUM(B) 'SUM of Amount'",1);
   "Grand Total", SUM(B:B) }

Example showing source data, results and formula

Grand Total at Top

You can also move the Grand Total to the top. This might be useful for long lists and also allows directly applying formatting including borders.

={ "Name", "Total Amount"; "Grand Total", SUM(B:B);
   QUERY(A:B, "Select A, SUM(B) Where A is not Null
     Group by A Label A '', SUM(B) ''",1) }

Example showing source data, results and formula

Find Missing Names

Searches Name column A for any values that do not also appear in pivot table Name column E and returns any matches as an array of values.

=FILTER(A:A, NOT(COUNTIF(E:E, A:A)))

Image Description

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.