Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 :) |
#2
|
|||
|
|||
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 :) |
#3
|
|||
|
|||
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 :) |
#4
|
|||
|
|||
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 :) . |
#5
|
|||
|
|||
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 :) |
#6
|
|||
|
|||
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 :) . |
#7
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a dynamic list | Excel Worksheet Functions | |||
Is there a way to create a drop-down list of links in Excel? | Excel Worksheet Functions | |||
The pivot tables 101 article says to use the "Create List" comman. | New Users to Excel | |||
create a drop down list with the source from a different workbook | Excel Discussion (Misc queries) | |||
Creating a dynamic list | Excel Worksheet Functions |