Turn Dynamic Horizontal Chart into 12-month rolling

**robbrown** · 02-13-2011, 05:49 PM

Hello I am having trouble turning the Dynamic Chart attached into a 12-month rolling chart. I am not very familiar at all with the Offset formula as well as Ranges and would greatly appreciate it if someone can help me. I created a very simple dynamic chart which has been modified from the most commonly known out there to work with horizontal data and Vlookups, thanks to Andy Pope from an older post linked below. I would like to know where to insert code into the Range formulas (CTL-F3) which would create a 12-month rolling chart. Thank you once again to any that could help!

Referenced old thread
http://www.excelforum.com/excel-char...tal-table.html

Vertical Dynamic 12-month rolling chart (w/out Vlookup solution modification)

**robbrown** · 02-13-2011, 05:53 PM

sorry forgot to paste the last link for a vertical dynamic 12 month rolling chart WITHOUT the Horizontal and Vlookup modification.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

**Andy Pope** · 02-14-2011, 05:47 AM

You need to calculate the number of columns to offset from the Anchor cell.
The Number of columns now becomes a static value, 12, rather than calculated.

CHTLABEL: =OFFSET(Sheet1!$B$22,0,COUNTIF(Sheet1!$B$23:$AZ$23,">=0")-12,1,12)

Not sure what the other data named ranges are suppose to be doing. Normally the simplest thing is to offset the CHTLABEL range.

CHTDATA1:
=OFFSET(CHTLABEL,1,0)

CHTDATA2:
=OFFSET(CHTLABEL,2,0)

**robbrown** · 02-16-2011, 03:23 AM

Brilliant!! Thanks so much Andy! Works Perfectly..

**allstar51788** · 08-29-2012, 08:30 AM

This is exactly what I need.

**Rasterty** · 04-09-2013, 12:57 PM

I was having problems with my graph and then I found this walk through. For some reason the one posted on Peltier I could not get working.

http://www.journalofaccountancy.com/...l/20125037.htm

**AlanC** · 12-27-2015, 04:54 PM

Hi, is it possible to have the date ranges, say months as the rolling series, so x axis is fixed as the subject headings and the series roll instead of the x axis, I cant figure it out

**FDibbins** · 12-27-2015, 05:22 PM

Alan Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.