How to apply a formula to an entire column?

[aspen_gal]

Hello, below is a sample of my excel sheet. The number of rows keep changing daily and the Month and year column has to be populated automatically as and when there is a new row inserted. I tried the formula =MONTH(A:A) and I paste it on the entire column. it works for rows with data but I also get 104,000 odd blank rows added to my excel because I pasted the formula on the entire column. I do not want to add rows with blank data. I tried IF (Date is blank then insert "" in Month else put the month(Column A). All it does is inserts several 1000s of rows with MONTH ="".

Date Month year
10/10/2009


Thanks much!

 

[phxsportz]

Just trying to get a handle on what your're doing.. is your sheet something like the following ? You have a cell that contains the entire date and you need to process and split it up into 3 cells ?/

Excel Workbook
	A	B	C	D
1	Whole Date	DAY	MONTH	YEAR
2	4/14/2009	14	4	2009
Sheet2

 

[aspen_gal]

yes sir! This is exactly what I want to accomplish, but my sheet will be loaded with data on a daily basis and I dont want to do the formula for every row added. I just want to create a template with the formula so that as the sheet grows, the formula is automatically calculated.
Thanks a bunch

 

[phxsportz]

How about just adding this macro (adjust the columns as required)

Private Sub worksheet_change(ByVal Target As Range)
    Application.EnableEvents = False
    For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        d = Cells(x, 1)         'cell with whole date   (rownum, "A")
        Cells(x, 2) = Day(d)    'clell to place the day  (rownum,"B")
        Cells(x, 3) = Month(d)  'etc
        Cells(x, 4) = Year(d)
    Next x
    Application.EnableEvents = True
End Sub

 

[aspen_gal]

Thank you and I just have to schedule this to run automatically..

My next question is besides a macro, isnt there any other way like an Auto fill or other Excel functions (no vba)?

 

[phxsportz]

Not sure on the NO VBA option, but how do you populate your worksheet ? are you importing the data from a csv or something ? If so, this should run when you do so

 

[aspen_gal]

There is a daily job that populates this sheet.