Hello all.
I am trying to get some help with a data file I was asked to look at. Initially I created a user form and wrote some VBA code to filter the data set by operator, date and class and give me a tally. I also added some additional calculations to tell me for each operator in a given quarter what % of their counts were non-zero values. This worked perfectly for quarterly reports, but now they would like to see the data updated in real time.
So..... I decided to try my luck with using a pivot table. I have never really worked with pivot tables so it took me a bit to understand how they worked. I have created the pivot table using a named range so that the pivot table would udpate as I added more data. I also added some vba code so that when I add new data and select the pivot table tab the data will refresh.
The part I am having trouble with is getting the % to work. For each operator, the count can be 0 or another number. I want to know what the % of non zero values are for any given operator when there are non-zero values present. The % of row and % of column options do not give me the result I am looking for since I just get a % of the Grand total. I searched forums and google to look for solutions and didnt find anything I could get to work. I would like to avoid having to add additional formulas to the data set if possible so that I dont have the issue of recalculating as there are over 40000 rows of data in the the complete data set.
Any help would be great.
Just added the Excel file, but I had to zip the file to get the upload to work.
Thanks
E
Bookmarks