Need shifting graph to only move is data is added

[arip1979]

Hi Everyone,

I really tried looking around for an answer before finally giving in and posting this. I found a lot of similar questions but none exactly like mine. It seems simple enough but I haven't found a solution.

So I have a very simple worksheet that is used for entering attendance for a weekly event. The last column has a formula that will just sum the previous 3 columns. Very simple. However, I am also graphing the SUM column in a graph that only shows the last 3 months. So here's my problem.

Let's say I have my attendance for Adults, Kids, Staff in columns A,B,C respectively. Column D will have the SUM.

I have tried putting this formula in column D: IF(NOT(ISBLANK(A1)),SUM(A1:C1),"") and then copying it down. This works great for not allowing the SUM to be visible unless there is data in column A. However, the chart still sees that there is a formula in column D and shifts the Graph without actually plotting anything.

So I get a chart that has the dates at the bottom with no lines on the graph. I need to be able to copy the formula down the column all the way to the end of the year but not have the graph shift unless the formula actually has a value. All of this is so the person who enters the attendance data will not have to manually drag the formula down each week.

Any help on this is greatly appreciated.

Thanks in advance,
Adam

[BadlySpelledBuoy]

Hi,

This can be done with dynamic ranges.
If you post some dummy data in a workbook I will show you how.

BSB.

[arip1979]

Hi,

This can be done with dynamic ranges.
If you post some dummy data in a workbook I will show you how.

BSB.

What's the best way to "post some dummy data in a workbook" Is there a good way to do this in this forum?

Thanks

[BadlySpelledBuoy]

Create the dummy file. Click on the "Go Advanced" button below your reply post and it should be self explanatory how to add a file there. Start with the "Manage Attachments" button below your post.

[arip1979]

Okay, wasn't sure if there was a way to put it directly in the reply text.

Here you go.

Sample.xlsx

[arip1979]

P.S. I'm already using dynamic ranges in the graph/chart to keep it from breaking when there isn't a full 3 months of data available (e.g. right now since we are only in February)