Hi,
I'm collecting data points on a weekly basis from multiple websites (number of articles). I started this work on two websites in 2006 and have since then added more websites as they've been established. It's now grown to 50+ websites. For each one I have a line chart that shows the growth since I started tracking. As I've grown tired of manually updating each chart, I want to set up dynamic ranges for each website.
I've made one range for the dates, and was planning on making one for each website. However, once I did this for website number three which I started collecting data for a year later than the first, the match between date and data was wrong.
How can I fix this?
My dynamic range formula for the dates which are just weekly dates starting 01/01/06 in the B column starting at row 3:
=OFFSET('Finn stat'!$B$3,0,0,COUNTA('Finn stat'!$B:$B))
For the first website the data also start at row 3, but in column N.
=OFFSET('Finn stat'!$N$3,0,0,COUNTA('Finn stat'!$N:$N)-1)
So far so good.
For the third website the data starts at row 65 in column AS as I started collecting this on the 08/01/07 - a year later than the first two. I've tried making the dynamic range start at both row 3 and row 65, but none of them gives the chart I want
If I use..
=OFFSET('Finn stat'!$AS$65,0,0,COUNTA('Finn stat'!$AS:$AS)-1)
...the line chart links the data to the original beginning data (010106).
If I use
=OFFSET('Finn stat'!$AS$3,0,0,COUNTA('Finn stat'!$AS:$AS)-1)
..the line chart X-axis begins at 01/01/06, and the data line begins first at the correct date 08/01/07. The chart also cuts off earlier than the actual data set (with the same amount of data points as there are empty cells in the beginning).
I could set up a custom dates range for this website, but as I have 50+ websites all starting at different dates, I would have to set up an equal number of dynamic ranges which would not be any more efficient.
Is there a way I can make the dates follow the data and not the other way around?
Many thanks.
Bookmarks