Rolling Formula based on If

[Kristine]

I have a graph that uses formula's gathering information from different
worksheets and other workbooks. The 'x' axis label inputs dates based on the
current formula which reads a specific set of cells from another sheet.
These dates currently are set and show an entire 12 month period or year on
each worksheet. (this can be changed)

My question: Is there a way to create a formula that will roll
automatically showing the previous 12 months of information from today's date.

example: If today is March 15, 2007, I want the graph to show March 15,
2007 thru March 15, 2006 of the previous year's input.

 

[Luke M]

Not sure if its the best, but I'd use
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

And change the number being subtracted for each month you want to go back.

 

[Herbert Seidenberg]

This approach will let you click on the year
that you want and display it on the graph.
Not exactly what you want, but you can probably
tailor it to your needs.
No VBA code required.
Familiarity with defined names helpful.
Assume you have 3 sheets named Y2005 to Y2007
and one named CData.
Each year has named data like this:
Year
2006

Date Val
01/01/06 1
01/02/06 1.63
01/03/06 2.32
01/04/06 2.41
01/05/06 2.70
01/06/06 3.28

On the CData sheet, you have similar data like this:
Name them as shown.

LegendS Spindex
2006 2006

DateS ValS
01/01/06 1.00
01/02/06 1.63
01/03/06 2.32
01/04/06 2.41
01/05/06 2.70

The formulas for LegendS, DateS and ValS are:
=INDIRECT("Y"&Spindex&"!Year")
=INDIRECT("Y"&Spindex&"!Date")
=INDIRECT("Y"&Spindex&"!Val")

Plot CData!DateS and CData!ValS
Source Data > Name > CData!LegendS
While on the chart page, get the Spinner
from the Forms toolbar and place it on the chart.
Right-click the Spinner > Format Control > Control >
Min Value =2005
Max Value = 2007
Cell Link = CData!Spindex
The Spinner will let you go back and forth
between the years and display that year.