Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Range (MS Excel 2003)

    On the attached workbook, I am trying to create a reporting template where users will enter reporting period and values and then have the charts dynamically update.

    You guys have been a tremendous help setting me straight on my OFFSET/COUNTA function. I've actually applied what I've learned about dynamic ranges to have the trending chart so that it will update automatically with new information.

    However, I want to have a dynamic chart that is going to illustrate only the most recent 4 reporting periods of information. I used the following arrays for the named ranges used in the historic trending chart.

    Period =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$13))
    Value =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$13))

    Despite my best efforts, I can't bastardize these to only give me the most recent four records and have it dynamically update with new entries. Any help is greatly appreciated.

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Range (MS Excel 2003)

    You can define the range for the x values as follows:

    =OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$B$2:$B$13)-4,0,4)

    and that for the y values like this:

    =OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$B$2:$B$13)-4,0,4)

    Notice that both use COUNTA(Sheet1!$B$2:$B$13). The 4 at the end indicates that the range is resized to exactly 4 rows. The offset is the number of populated cells in B2:B13 minus 4.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Range (MS Excel 2003)

    This formula works well. Can it be modified to show 4 periods of data only if there are 4 periods of data present in the table? That is, can it do better to accommodate 1, 2 and 3 periods of data entry if that is all there is? Thanks.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamic Range (MS Excel 2003)

    How about:

    =OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$B$2:$B$13)-MIN(4,COUNTA(Sheet1!$B$2:$B$13)),0,MIN(4,COUNTA(Sh eet1!$B$2:$B$13)),1)

    =OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$B$2:$B$13)-MIN(4,COUNTA(Sheet1!$B$2:$B$13)),0,MIN(4,COUNTA(Sh eet1!$B$2:$B$13)),1)

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Range (MS Excel 2003)

    As I expand to more complicated charts I ran into a problem. The X-axis labels won't show up if I have a line chart with multiple series.
    Periods in column A
    Values in Column B, C, D, E

    HistoricPeriods =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$13) (X-axis label)
    HistoricValuesSeries1 =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$13)
    HistoricValuesSeries2 =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C$2:$C$13)
    HistoricValuesSeries2 =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$13)

    I only want as many X-axis labels as their are values for one of the series.

    I get a similar behavior from the last two equations that show only a 4 periods of values. What gives?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Range (MS Excel 2003)

    Could you attach a workbook demonstrating the problem?

Posting Permissions

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