Formula automatic changes to cell refernces

[davidfn]

Hi

I'm using an equation to work out age based upon DOB when case opened.

I have three columns Formula, DOB, Date case opened.

When either the DOB or date case opened is cut and pasted further down the column, the formula refernce numbers follow the dates. There is no need to cut and paste the dates and the users have been given instructions not to. However, people are poeple, so if i can stop excel from doing this it would be prefererable. I had thought that using the $ sign in the formula might help, but I need to copy the formula into 1000 cells so need excel to be able to change the references to do this. I'm using excel 2003 and the equation is as follows:




Any suggestions? Thanks in advance

Nick

 

[SamTYler]

Change the formula in the top cell, select it and the 1000 cells below, then type ctrl+D.

ps: No formula followed in your OP.

 

[davidfn]

Hi

Sorry for missing the formula and perhaps for not explaining myself. The problem stated again by example:

row1 - (a2)=IF(COUNT(H2,F2)=2,DATEDIF(H2,F2,"y"),"") - (H2)DOB, (F2)Date Case opened
row2 - (a3)=IF(COUNT(H3,F3)=2,DATEDIF(H3,F3,"y"),"")- (H3)DOB, (F3)Date Case opened
row3 - (a4)=IF(COUNT(H4,F4)=2,DATEDIF(H4,F4,"y"),"")- blank

The problem is, if someone were to cut and paste (h2) dob into row h4 blank cell, the formula (a2) in row 1 changes from h2/f2 into h4/f4. How can I stop this from happening, in a column that needs to have the formula in 1000 rows.

Thanks again

 

[SamTYler]

idunnonothing, but you can try this in the workbook open macro, or if you need to allow some cut/copy paste, in the selection change event macro.

Application.CutCopyMode = False