Hello
I wonder if someone can help with a quick query. I’m using Apple Mac by the way
I have a simple spreadsheet with data on one tab and a nice dashboard layout on the second. A pivot table extracts customer names from the raw data and populates the dashboard.
The problem is when a new customer name is added to the data tab, it does appear in the list in the Pivot table. There is no “refresh” option either, by right clicking in the pivot table itself and selecting refresh.
So I’m at a loss to get the Pivot table to update itself from the raw data.
If anyone knows the answer I would be very grateful!
Many thanks indeed
Greg
Hi @GregV , maybe it’s not necessary to be PivotTable, post an example file, it’s easier to suggest a solution.
Hello Sir!
Many thanks for replying.
I’ve tried to attach a file but it is in .xlsm format (it was originally built on excel) and it is not a supported format for uploads. I’m very sorry. Any help still would be great.
Thanks very much.
Greg
Can you save as an ODS and upload as an edit to your original question?
If it turns out to be a pivot table, or you get it repaired to be one, see Calc - Create two pivot tables from the same source - #3 by joshua4 for an example file that has a button on the dashboard to update the first (usually, only) pivot table on each of specific sheets, or follow on to the eeigor solution on the same link for an example file that has a macro that updates all pivot tables on a given sheet.
Hello Joshua
I’ve saved as ODF and uploaded it. I’ve had to redact quite a bit of data but the problem should be clear still. The pivot table does not refresh when a new name is added to the data tab
Thanks so much
Greg
Client database=libra help sample file.ods (162.2 KB)
1 Like
Just to add Joshua,
If I could click “Data” - “Pivot Table” - “Refresh” I’m sure that would work. But the “Refresh” option is greyed out!
Because there is no pivot table. The cells on that sheet were created as a pivot table but something has destroyed the pivot. Delete that sheet and create a new one.
Thanks Villeroy
I would do that but is beyond my ability I’m afraid. If the pivot table has gone I would not know the code or how to recreate it.
Many thanks for looking at it
Best wishes
Greg
Hi, could try and replace the Pivot table with Data validity,
On your client statement tab, click in the client name selection box, then Data, Validity, from the drop down box, cell range, in the source box put $Data.$B$7:$B$8000 so it looks like this,
and press OK.
I obviously don’t know many rows in your spreadsheet, but if it is more than 8000 then just increase the range.
Let us know if this helps.
Produced in Windows 10 home, LO 7.3.0.3
Hi gregors15
Many thanks indeed for your note.
That almost works!
However, rather than just picking up the names from column B on the Data tab, it is listing all the data in the data tab! So on the live file that is an awful lot of data that is in the list in the client name selection box on the Client Statement tab. If we can somehow confine it to just picking up the names in column B then that should work fine.
Thank you so much for helping
Kind regards
Greg
Hi gregors15 again
I’ve tried it again and it seems to work fine this time around!
Many thanks indeed for your suggestion and solution.
I’ll let you know how it goes.
Best wishes
Greg