I want to create a rolling column in Excel 1

[avatarp]

I want to have the first day of the current month displayed in A1 and the following months displayed beneith. So when the next month happens the columns rolls up one to displaythe next current month

 

[xlhelp]

Hi!

In the first cell type

=EOMONTH(NOW(),-1)+1

In the next cell type
=EOMONTH(NOW(),COUNTA($A$1:A1)-1)+1

and copy down as far as you want.

You will need to format the cells.

HTH

Indu Member AAAA (Association Against Acronym Abuse)

 

[danvlas]

Try this:

A1 = TODAY()-DAY(TODAY())+1

case 1: you want only the month displayed
A2 = IF(MOD((MONTH($A$1)+ROW()-1), 12)=0,12,MOD((MONTH($A$1)+ROW()-1), 12))
Drag down as needed
From A2 down, the cells should be formatted as numbers

case 2: you want the first day of next month displayed
A2 = DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Drag down as needed


Dan

P.S. xlhelp: using EOMONTH function causes an #Name error. Is it a user defined one?

 

[xlbo]

Nope - EOMONTH is part of the Analysis Toolpak (M$ addin) - just go to Tools>Add ins and tick the box next to it - it should be there already - you just need to select it - it'll give you a set of extra worksheet (and vba) functions
Rgds
~Geoff~

 

[avatarp]

The best solution is:

A1= EOMONTH(NOW(),-1)+1
A2= EOMONTH(A4,0)+1
Copy A2 down as far as need be.

This works the best because there are very few call to functions so this will be a very tight and stream line.