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 :)

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 wrote in 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 :)

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 :)

.

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 <atyahoo<dotcom
----
"marika1981" wrote in 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 :)

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 :)