+ Reply to Thread
Results 1 to 6 of 6

Excel OFFSET for Dynamic Chart

  1. #1
    Registered User
    Join Date
    12-30-2014
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    2

    Excel OFFSET for Dynamic Chart

    hi there,

    I've been a silent reader for some time now and finally registered today, as I'm struggling with an issue;

    I'm trying to adapt one of my charts(sheet = Overall D-CH) to only show the last 12 months by using the OFFSET function. apparently I haven't been able to work it out so far.

    thanks in advance,
    michael

    Offset_Last12Months.xlsx

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Excel OFFSET for Dynamic Chart

    I'm assuming you mean the past 12 months from today's date, even though your data range extends to Dec 16?
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

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

    Re: Excel OFFSET for Dynamic Chart

    Here's how I would set up these dynamic ranges.
    First, I'd make one for the x axis or dates
    Dates = OFFSET($D$4,0, MAX(0,MATCH(TODAY(), $D$4:$ZZ$4)-12),0,12)
    MATCH(TODAY(),D4:ZZ4) finds the last date less than todays date which would be your theoretical end of data point
    The Max(0, Match...) is in case you have less than 12 columns of data
    The others can be based off that series so you don't need to re-invent the wheel. For example, Mobile New is 9 rows below Dates
    Mobile_New = OFFSET(Dates, 9,0)
    and so on.
    Last edited by ChemistB; 12-30-2014 at 12:47 PM.
    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

  4. #4
    Registered User
    Join Date
    12-30-2014
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    2

    Post Re: Excel OFFSET for Dynamic Chart

    Indeed, I didn't express myself very well. I want to show the last 12 Months having Data other than 0 (<>0) for Rows 13 to 18.

    @ChemistB
    appreciate your feedback. Somehow I'm not able to assign the data within the chart (Offset_Last12Months_v2.xlsx‎)...seems like not able to make it work
    Attached Files Attached Files

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

    Re: Excel OFFSET for Dynamic Chart

    Attached is your original workbook where I substituted my Named Ranges for yours. It uses "Today" as an end point so if you are behind in data entry, it will have zeros at the end. We could work out another way if this is not satisfactory
    Attached Files Attached Files

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

    Re: Excel OFFSET for Dynamic Chart

    Just looked at your V2 sheet. A couple of things I picked up on.
    One, in the Dates Offset formula, you end with 0, 12) It should be 1, 12) (one row high, 12 columns wide)
    Your Ezsatz offset is off by 1 row

+ 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. Dynamic roll chart with formula?? how to test (offset) it?
    By mario17 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-05-2014, 06:27 PM
  2. Dynamic chart with Offset not working? Volitale??
    By trillium in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 05-13-2013, 12:16 PM
  3. Dynamic Chart - Offset function
    By jantonio in forum Excel General
    Replies: 2
    Last Post: 10-17-2011, 02:49 PM
  4. offset/dynamic chart
    By TWDC in forum Excel General
    Replies: 3
    Last Post: 04-11-2011, 04:58 AM
  5. OFFSET problem: Changing dynamic chart
    By perra in forum Excel General
    Replies: 1
    Last Post: 02-06-2009, 05:57 AM

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