How can I create an automatically updating chart from a different worksheet with blank spaces in excel 2010

Question

I have a worksheet that lists every day so I can see if any tests were performed that day. If there were tests, then the average and standard deviation of that day show up in the next two columns. I also break up the days by adding rows of weekly averages.

I need to make a graph that only graphs the days averages and will automatically update if any new information is put in. As I understand it I can not use the OFFSET function because my information is not in a continuous line. The only solution I can think of is to filter out all of that information in a chart that automatically updates when new information is entered in the original worksheet, which will in turn update the graph.

How can I do this?

Answer 1

From what I see in your picture, I think the easiest way to make a chart is to build a table of averages in a different sheet. Reference the weekly averages from the "Resilience" sheet to the table. Then select the data in the new table to make the chart (example below).

When you update the data on the "Resilience" sheet the table and the chart will update. In the below example I added data to the week of 10/13 and the chart automatically updated.

Keep in mind, to have things update automatically, you will need to have auto calculations enabled in options. Otherwise, press F9 to update calculations manually.