Recognizing new rows in a chart?

**GetYours** · 01-27-2010, 10:55 AM

Hi everyone,

I am trying to make a chart that recognizes changes in data and the addition of new data. I am collecting data and have it laid out in two columns. Date and data. I have created the chart and it is fine. However, the problem is every week I am collecting new data, and add it by inserting a new row at the top of my data field. The chart does not recognize this new row, and skips over it in the data field. It will recognize old rows above and below, but now the new inserted row. Since I have at least 10 types of data with 10 charts I don't want to have to go through each one changing the formulas. Please any suggestions would be great!

**Andy Pope** · 01-27-2010, 11:20 AM

If you turn your data range in to list then inserted rows will automatically appear in the chart

**GetYours** · 01-29-2010, 04:48 AM

Thanks Andy, your solution worked perfectly.
I have run into a second problem however. In the same set of data, I want to calculate the growth in increments of 1 year, 6 months, and 3 months. I don't have a problem putting the formula in, but everytime I add a new row of data, the formula moves down with the same cell, so the growth measures the same year period instead of the new period.
For example, 1 year growth between Jan 1st 2010, and Jan 1 2009 is fine, but when I add a row for February 1 the formula moves down and stays on Jan 1st.
Sorry if this is long I just wanted it to be clear.

Best,

Get Yours

**Andy Pope** · 01-29-2010, 05:23 AM

Hard to tell.
Does your formula include absolute references?

Can you post example to illustrate the problem

**GetYours** · 02-04-2010, 10:44 AM

Hi Andy,

Please find attached an example using currencies. I want the growth formula to always be on row 9, and also the respective row for the rest of the calculation. The problem is the formula slides down with the data instead of staying on a single row. I understand the logic in this, but in this case I would like it the other way.

Best,

GetYours

**Andy Pope** · 02-04-2010, 11:20 AM

You can use formula to detemine which dates are 12, 6 and 3 months different.

In D2 is the value 12. D3 is 6 and D4 is 3

E2: =DATE(YEAR($A$9),MONTH($A$9)-D2,DAY($A$9))

You can then attempt to match the date with those you have in column A

This handles the problem of the reverse order of your data.

=MATCH(LARGE(IF($A$9:$A$66<=E2,$A$9:$A$66),1),$A$9:$A$66,0)

The workbook will make it easier to follow.

**ystress** · 02-05-2010, 09:32 PM

I have a macro that is run every day. The macro reads a dataset from a another spreadsheet and reformats to the main worksheet. It then takes a copy of the summary data and stores it on another tab with a new row for each day's work of data. I have charts on the first sheet that I want to automatically include the data from the new rows. I used the following commands, but the chart does not update. Any thoughts?

ActiveChart.SetSourceData Source:=Sheets("Historic").Range("A1:A" & Z & ",I1:K" & Z)

**teylyn** · 02-06-2010, 01:12 AM

Ystress,

welcome to the forum. However, your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.