auto fill rows with months based on cell value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Drew - 31 Mar 2008 07:53 GMT

Hello All ...

I am building a basic contract tracker spreadsheet.

In one cell I enter the start date and another the number of months of the
contract.

Currently I manually enter the first month (formated Mar-08) in the first
row then manually drag-fill it down to fill the corresponding number of
months of contract.

Is there a way to have Excel 2007 automatically fill this column with the
months based on the value of start date and number of months?

Thank you.

Reply to this Message

Max - 31 Mar 2008 08:09 GMT

Assuming A1 will house the # of months, eg: 3
and A2 contains your 1st-of-month date, eg: 1-Mar-2008, formatted as: mmm-yy
In A3:
=IF(ROWS($1:1)>A$1,"",DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1),1))
Copy A3 down to cover the max expected number in A1

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hello All ...
>
[quoted text clipped - 11 lines]
>
> Thank you.

Reply to this Message

Drew - 31 Mar 2008 08:22 GMT

Max ... it works a treat thank you ... excepting if I insert 1/3/08 as start
date the first auto complete month reads Apl-08. I need it to be the same
month as the start date month.

> Assuming A1 will house the # of months, eg: 3
> and A2 contains your 1st-of-month date, eg: 1-Mar-2008, formatted as:
[quoted text clipped - 17 lines]
>>
>> Thank you.

Reply to this Message

Drew - 31 Mar 2008 08:26 GMT

Max ... I worked it out ... changing the 1 at end of formula to '0'.

Thank you again very much.

> Max ... it works a treat thank you ... excepting if I insert 1/3/08 as
> start date the first auto complete month reads Apl-08. I need it to be the
[quoted text clipped - 21 lines]
>>>
>>> Thank you.

Reply to this Message

Max - 31 Mar 2008 08:40 GMT

No prob. I posted another way in reply.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Max ... I worked it out ... changing the 1 at end of formula to '0'.
>
> Thank you again very much.

Reply to this Message

Max - 31 Mar 2008 08:35 GMT

Adjust it a little
Use instead in A3, copied down:
=IF(ROWS($1:1)>A$1,"",DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Max ... it works a treat thank you ... excepting if I insert 1/3/08 as
> start date the first auto complete month reads Apl-08. I need it to be the
> same month as the start date month.

Reply to this Message