Thread: Dynamic Range (MS Excel 2003)

1. 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

2. 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. 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. Re: Dynamic Range (MS Excel 2003)

=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. 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. 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
•