+ Reply to Thread
Results 1 to 4 of 4

Dynamic ranges with different start date

  1. #1
    Registered User
    Join Date
    04-29-2013
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2010
    Posts
    7

    Dynamic ranges with different start date

    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.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Dynamic ranges with different start date

    You'll need different dates for each graph/website.
    Okay, let's say your dates are in Col A, WebsiteA is in B, Website B is in C and so on.

    Let's look at Website C in Col D

    The dynamic name could be

    WebDataC=OFFSET(D2,MATCH(TRUE,INDEX($D$2:$D$1000<>0,),0)-1,0, COUNTA($D$2:$D$1000),1)
    Then the dynamic name for the dates, (we'll call it dates_C)
    =OFFSET(WebDataC,0,-3)

    Make sense?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    04-29-2013
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Dynamic ranges with different start date

    Thanks, yeah makes sense - I think.

    I'll have a look at it when back in the office on Monday, but may have to come back here for more help. Didn't quite grasp the entire formula, but I realise I still have to create one dynamic date per website?

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Dynamic ranges with different start date

    I realise I still have to create one dynamic date per website?
    Depends. If you know A,B and C all start on the same date, then you can use 1 x axis for those, then one for D,E and F (which start on another date) and so on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 04-23-2013, 01:14 PM
  2. Make date ranges for graph (choose start and finish date)
    By zeko90 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2013, 09:13 AM
  3. [SOLVED] Counting the number of times a date occurs in multiple start and end date ranges.
    By Grizz in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-05-2013, 04:41 PM
  4. [SOLVED] Dynamic named range based on a start date and end date
    By Gary Lockton in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-06-2012, 07:26 PM
  5. Replies: 0
    Last Post: 03-30-2012, 01:56 PM
  6. Changing start point in Dynamic Named Ranges for a Chart
    By carsto in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-13-2009, 10:12 AM
  7. Moving ranges by start date
    By ashleyfox in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2005, 06:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1