Thread: Formula result is making me nuts!

The formula: =IF(MONTH($A4)=COLUMN()-6,$E4,0) is in COLUMNS: G, H, I, J etc.
The outcome: #VALUE!


_______A__________E_________G________H
4___1-10-2010____$22.22______Jan_______Feb_____ etc...

This is a XL 2008 LIST.

Column A is formatted as date. Column E is Currency.

The whole thing, Col A through R is a LIST.
The above formula is in the columns designated JAN, FEB, MAR etc...

When I put a value in the month field, I get #VALUE! across all the months where the above formula is located.

If I go into EDIT mode on a formula AND CHANGE NOTHING, I am asked if I want to make that column a formula column and when I choose YES, everything is normal.

This is a simple formula and each element is working correctly. But this weird anomaly is driving me nuts!

Can someone please give me some insight on what is going on???

Thanks

I am sorry for the misuse of the forum. I tried to edit the subject of my post but that doesn't seem possible. Sorry.

As for the content of the post. Here is the 'new' situation.

I created a NEW LIST from scatch.
In the farthest columns to the right I have Jan Actual, Feb Actual ... etc.

In those months is the formula: =IF(MONTH($E9)=COLUMN()-6,$D9,0)

But, when I make an entry in Column A popup, the entire Months Columns goto #VALUE! .

IF I EDIT (that is put the cursor in the edit formula, change NOTHING, and just press ENTER and accept that the formula will be reproduced in the column... everything comes back as it was. From this point, I can add all the new rows as I want and will NOT get the error.

But, if I save the WS, Close it, Reopen it, and add a new row.... When I add something to Column A, I again get #VALUE!!!

__A_____B____C _____D_______E_______F_______G______H
Account_____________Amt_____Date_____________Jan_____Feb