how to create a dynamic descending list of months & years

I'm trying to create a spreadsheet whereby in cell A1 I enter in any given
Month and Year ("January 2005", formatted as a date), and in cells B1:J1 the
previous months and years show up (December 2004; November 2004; October
2004; etc....) If I type in "April 1985", the adjacent cells would read
"March 1985; February 1985; January 1985; December 1984; etc...

I've thought of a couple laboriuos, crude, potentially faulty ways of doing
this, but it seems like there should be an eloquent solution.

Very much appreciated!!!
Marika

Can you put the following formula in B1:
=date(year(A1),2,1)
the following in C1:
=date(year(A1),3,1)
etc. this will create a date with the year that is in A1 and the second
parameter specifies the number of the month...

"marika1981" wrote:

> I'm trying to create a spreadsheet whereby in cell A1 I enter in any given
> Month and Year ("January 2005", formatted as a date), and in cells B1:J1 the
> previous months and years show up (December 2004; November 2004; October
> 2004; etc....) If I type in "April 1985", the adjacent cells would read
> "March 1985; February 1985; January 1985; December 1984; etc...
>
> I've thought of a couple laboriuos, crude, potentially faulty ways of doing
> this, but it seems like there should be an eloquent solution.
>
> Very much appreciated!!!
> Marika
>

Enter the 1st day of the month in A1 in the format
m/d/yy, then customer format as mmmm yyy.

In B1 insert this:

=DATE(YEAR(A1),MONTH(A1)-1,1)

and fill across. Format the cells as mmmm yyy.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I'm trying to create a spreadsheet whereby in cell A1 I
enter in any given
>Month and Year ("January 2005", formatted as a date),
and in cells B1:J1 the
>previous months and years show up (December 2004;
November 2004; October
>2004; etc....) If I type in "April 1985", the adjacent
cells would read
>"March 1985; February 1985; January 1985; December 1984;
etc...
>
>I've thought of a couple laboriuos, crude, potentially
faulty ways of doing
>this, but it seems like there should be an eloquent
solution.
>
>Very much appreciated!!!
>Marika
>
>.
>

you mut have entered some date in dec e.g. 12/1/2005 in A1 and format it as
december-05
now highlight A1 to J1
edit-fill-series-date and check month stepvalue -1 (minus one) and clcik
ok

try this and see what you get



marika1981 <[email protected]> wrote in message
news:[email protected]...
> I'm trying to create a spreadsheet whereby in cell A1 I enter in any given
> Month and Year ("January 2005", formatted as a date), and in cells B1:J1
the
> previous months and years show up (December 2004; November 2004; October
> 2004; etc....) If I type in "April 1985", the adjacent cells would read
> "March 1985; February 1985; January 1985; December 1984; etc...
>
> I've thought of a couple laboriuos, crude, potentially faulty ways of
doing
> this, but it seems like there should be an eloquent solution.
>
> Very much appreciated!!!
> Marika
>

One way

With A1 containing, say: January 2005

Put in B1:

=DATE(YEAR($A1),MONTH($A1)-COLUMNS($A$1:A1),1)

Format as per date in A1

Copy B1 across as many cols as needed

The above will return what is wanted
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"marika1981" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to create a spreadsheet whereby in cell A1 I enter in any given
> Month and Year ("January 2005", formatted as a date), and in cells B1:J1
the
> previous months and years show up (December 2004; November 2004; October
> 2004; etc....) If I type in "April 1985", the adjacent cells would read
> "March 1985; February 1985; January 1985; December 1984; etc...
>
> I've thought of a couple laboriuos, crude, potentially faulty ways of
doing
> this, but it seems like there should be an eloquent solution.
>
> Very much appreciated!!!
> Marika
>

Jason - thank you!!! That worked perfectly!

Breathing easier,

Marika

"Jason Morin" wrote:

> Enter the 1st day of the month in A1 in the format
> m/d/yy, then customer format as mmmm yyy.
>
> In B1 insert this:
>
> =DATE(YEAR(A1),MONTH(A1)-1,1)
>
> and fill across. Format the cells as mmmm yyy.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I'm trying to create a spreadsheet whereby in cell A1 I
> enter in any given
> >Month and Year ("January 2005", formatted as a date),
> and in cells B1:J1 the
> >previous months and years show up (December 2004;
> November 2004; October
> >2004; etc....) If I type in "April 1985", the adjacent
> cells would read
> >"March 1985; February 1985; January 1985; December 1984;
> etc...
> >
> >I've thought of a couple laboriuos, crude, potentially
> faulty ways of doing
> >this, but it seems like there should be an eloquent
> solution.
> >
> >Very much appreciated!!!
> >Marika
> >
> >.
> >
>

Sorry, didn't read it close enough.

"Nick B" wrote:

> Can you put the following formula in B1:
> =date(year(A1),2,1)
> the following in C1:
> =date(year(A1),3,1)
> etc. this will create a date with the year that is in A1 and the second
> parameter specifies the number of the month...
>
> "marika1981" wrote:
>
> > I'm trying to create a spreadsheet whereby in cell A1 I enter in any given
> > Month and Year ("January 2005", formatted as a date), and in cells B1:J1 the
> > previous months and years show up (December 2004; November 2004; October
> > 2004; etc....) If I type in "April 1985", the adjacent cells would read
> > "March 1985; February 1985; January 1985; December 1984; etc...
> >
> > I've thought of a couple laboriuos, crude, potentially faulty ways of doing
> > this, but it seems like there should be an eloquent solution.
> >
> > Very much appreciated!!!
> > Marika
> >