Hi
My problem statement:
Based on a sheet named 'DATA' that gets refreshed everyday , I have created a pivot using Insert-Pivot table option. Pivot is set to refresh automatically on opening file.
I made a calculated field called 'ORDER STATUS' with formula =IF('SUM(OrderQuantity)'+'SUM(DeliveryQuantity TNE)'<>0;0;999) and then I make a filter on calculated field by clicking on an empty field next to the pivot and use Data-Filter option and then the filter icon appears on my calculated field where i tick only the option 0 and leave the 999 entry unchecked. In this way manualy get only the order status = 0 entries
Next time when the I open the excel, the DATA sheet is correctly populated but the ORDER STATUS shows some entries with 999 . if i manually clear the filter on this calculated field and set it again then it works but usually we would not open this file as it will be directly sent to the user and want this field to always retain the filter on 0 and rows with Order status = 999
I dont need to show this Order status to the end user , It can be hidden but to test and to trust that i am filtering only on 0 , i have kept it visible,
Is there a way to do this? I am a basic excel user . Appreciate guidance from this group.
Bookmarks