Data mapping for charts in serise.

Posted by Mitul Rao on April 16, 2001 9:27 AM

Hi,
I have charts mapped to the data which chages every month because my charts are for last 12 months. Every month I have to manualy copy past last 12 months to the chart's data mapping. Is there any way to automate the mapping. If any questions/confusions please drop me a line.
Mitul

Posted by Dave Hawley on April 16, 2001 8:41 PM

Hi Mitul

Providing I have understood you correctly you can use a Dynamic named range.

1. Go to Insert>Name>Define
2. Type a name in the "Names in workbook" box
3. In the "Refers to" box put this formula:

=OFFSET($A$1,0,0,MONTH(TODAY()),1)

4. Click Add, then OK.

This range will exand down by 1 row every month. So as it is now April the range will incorporate A1:A4 (4th Month). You can easily expand the range to cover more than just Column A by changing the last "1" to a 2 or more.

I have some more examples of dynamic ranges and Chart tips on my Website under "Dynamic Ranges" and "Chart Tips and Tricks"

Dave

OzGrid Business Applications

Posted by Mitul Rao on April 18, 2001 5:43 PM

I tried the formula though I am not sure, how to make it work. It does not work. Can I get a little more assistance Please. How do I make charts to do what I want. Update the last 12 months.
Mitul

Posted by Mitul Rao on April 18, 2001 5:44 PM