04-06-2020 07:05 PM
04-06-2020 07:05 PM
I have two versions of my workbook here, version 3 and 4 attached here. I created version 4 to see if I could get rid of the problem, basically I cant get rid of the blanks that appear on my pivot table. As I add new rows to my database on sheet 1, I get these blanks on the pivot table.
I want to give this workbook to someone with no data yet in the sheet 1 datatable. They will simply be adding a date in the date column, dollar amount in the "invoice" column, invoice number and client name.
They should then click on the pivot table in sheet 2, refresh all or CTRL ALT F5 to update the pivot table and graph (version 3 workbook).
but these BLANKS are driving me nuts.
the other problem is, as I add more data to sheet 1, the data seems to appear in the FIELD LIST FILTERS but UNTICKED. Why is that? Even if I Refresh all, the fields remain unticked and dont appear inside of the pivot table.
I didnt have that problem before.
04-06-2020 09:37 PMSolution
I suggest you start from scratch. In version 3, delete the formulae at the bottom of the data table (rows 10 to 42). These cause Excel to see 33 records with a blank date. Hence, it adds a blank year (the check box without a year number) to the filter window.
Now convert your data table (Ctrl-T) into a so-called structured table. If you now enter a new date in column E, the table will automatically expand and copy down the formulae from the previous row.
Then, change the data source for the PT to this table, called "Table2" in the attached example. It will also avoid blanks. Referring column A:H is okay as well, but then you'll end up with the blanks that need to be filtered out.
04-07-2020 05:50 AM
Awesome Riny I will work on this and get back to you and thanks for attaching the sample ive tried it and it works well !!
by Ingeborg Hawighorst on May 13, 2020
by Brian Jones (OFFICE) on May 06, 2020
by cuong on April 15, 2020