+ Reply to Thread
Results 1 to 2 of 2

OFFSET problem: Changing dynamic chart

  1. #1
    Registered User
    Join Date
    01-17-2009
    Location
    Beijing, China
    MS-Off Ver
    Excel 2003
    Posts
    1

    Question OFFSET problem: Changing dynamic chart

    I am using a dynamic chart that shows a series of values for a 12 month period on a rolling basis. The OFFSET formula I use is

    =OFFSET('2.1'!$A$2,0,COUNTA('2.1'!$A:$A)-1,1,-MIN(chtLen,COUNTA('2.1'!$A:$A)-1))

    with the chtLen being 12

    This works well for data sets that have the dates in one column. When I add a new column the chart will be updated so that the latest 12 months are included in the chart.

    My problem is that I have a second set of data that has the months listed in a row. I want to make a new, similar chart and tried to change the formula to make the chart categories move dynamically in a row as opposed to down the column:

    =OFFSET('2.1'!$A$2,0, COUNTA('2.1'!$A:$A)-1, MIN(chtLen,COUNTA('2.1'!$A:$A)-1))

    It failed miserably.

    Any ideas appreciated
    Last edited by perra; 02-05-2009 at 11:17 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: OFFSET problem: Changing dynamic chart

    If your data is in a row going left to right then you need to change the COUNTA reference to that row rather than the column.

    =OFFSET('2.1'!$A$2,0,COUNTA('2.1'!$2:$2)-1,1,-MIN(12,COUNTA('2.1'!$2:$2)-1))
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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