Hello Everyone,
I have a workbook where I pull data in the "Detail" sheet to create a pivot table and chart in the "Pivots" sheet. I've attached a copy of the workbook. Ultimately I'm trying to create a chart that shows the percentage of work orders from this week that showed up in 1, 2 and 3+ previous weeks. Each week there will be about 100 rows of new data added to the "Detail" sheet.
If you look on the "Pivots" sheet of my workbook you'll see that starting at A:17 I have a formula =IF(F:F=1,G:G,""). This formula gets me the work orders from this week and the total number of times the work order has shown up in previous weeks. The problem with this is that next week there will be another column of data entered for the week of 1/28/13 so I have to use a find/replace operation to change my formula to =IF(G:G=1,H:H,""). This change to the formula will need to be repeated each week. That is the part I'm trying to avoid. Additionally, you'll see that I have range A1:B5 where I summarize my data for the week. I use that range to create the chart.
So with all that said, is there an easier way to get to that final end result of the chart each week using just the pivot table? Or, is there a way to set up my formula where I don't need to do the find/replace as the pivot table expands?
Thanks in advance for any help you can provide.
Bookmarks