How to better update this graph each week...

[eemiller1997]

I am trying to customize this excel worksheet. I have to fill this out for work and one supervisor requires the running list while the other supervisor requires the table titled, “IE’s”. I’m trying to figure out the best way and easiest way to work this IE graph. I have to fill it out by week and so the reference lines keep changing. For example, one week it’s from line 7-11 and the next 13-25, etc. There are 12 cells in this graph and it’s tedious and can cause errors for me to have to update it each week. Is there an easier way to update those cell references? There must be a way…..

I'm attaching an example of what I'm talking about.

2015 Testing Track Sheet-01.xlsx

[Melvinrobb]

I highly recommend using a Pivot Table to summarize weekly info.

The first summary tab is a pivot table, with a drop-down to select week 2, or 3, etc...

The second summary tab is using formulas, and you simply change the value in B3 to the week # you are wanting to summarize.
The negative of this is the formulas take much more work to maintain, and are much more prone to errors.

[eemiller1997]

There's only a couple of problems with that solution. I don't know pivot tables, so there would be a huge learning curve. By your explanation the formula of a pivot table would be just as cumbersome, if not more, than my current method. Probably much more since I'd have to learn them. And, finally, the bosses want the worksheet to look like it did in the example. There are about 50 of us submitting the same worksheet and they must combine them, so they don't want it to look differently by each person. They don't mind me adding formulas for my convenience, but not entirely changing the look as was done in the provided solution.

Another issue is that each week will have a different number of rows committed to it which is simply based on my productivity for that week, so one of the problems I'm trying to find a solution for is how to more easily define those weeks within my table. Because, the table itself can only cover one week at a time. After that week is over, we never care about that week's table info again. It does need to stay in the running count, but not within the table.

[Melvinrobb]

Pivot tables have NO formulas. They are much more dynamic, and exist specifically for the purpose of summarizing data in the way you are wanting to summarize it. I suggest investing a minimal amount of time to learn how they work, so you can make a more educated decision on whether to use them or not.

When it comes to the second solution I provided you, you can take the logic in that file in the attached example and use it however you see fit.
The only change I really made was adding a third evaluation to your countifs function, that included the date.

All the other changes I made are ones that I still suggest as they will avoid other issues you may run into. But they are not necessary to solve the issues you described.
So you make the range of the countifs cover the entire range, but the formula only count entries for a specific week (or if you not allowed to add a week # column, between two specific dates).
Make that single change, and it should be easier to update.