Looking to Simplify Pivot Table and Formulas Used to Create Chart

**mo4391** · 01-29-2013, 02:48 PM

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.

**FDibbins** · 01-29-2013, 03:17 PM

see if this is something you can work with?

put this anywhere (except column A) - I used B14...
=COUNTA(B16:G16)-2

then replace your formulas in A with this, copied down...
=IF(OFFSET(B17,0,$B$14,1,1)=1,OFFSET(B17,0,$B$14+1,1,1),"")

**mo4391** · 01-29-2013, 06:02 PM

Hi FDibbins,

After working with your formulas I realized I just had to make one small tweak to get it to do exactly what I needed. I changed the formula in B14 from =COUNTA(B16:G16)-2 to =COUNTA(B16:ZZ16)-2. That change seems to cover me for the added column each week (add least for the next few years).

Thank you very much for your help.