Formula changes when row is pasted in a different worksheet
 

Hi all,
I have a workbook with 2 identical worksheets. When data in one row in the
1st worksheet is no longer needed, I cut the whole row and paste it in the
second worksheet. I noticed that when i do so, the formulas change. For
example, i have a formula in the 1st worksheet to calculate a person's age:

=IF(E:E="","",(TODAY()-E:E)/365)

When i cut the row that contains this formula and paste it - say in row 30
of the second worksheet, the formula automatically turns into:

=IF(E$30:E$30="","",(TODAY()-E$30:E$30)/365)

The result of both formulas is the same. However, an a few occasions, the
formula changed to something like:

=IF(E$26:E$27="","",(TODAY()-E$26:E$27)/365) (which refers to 2 different
rows: 26 and 27)

The result for that last formula however was #VALUE!

I would like to know why this happens and how i can possibly prevent it.
Many thanks

Formula changes when row is pasted in a different worksheet
 

The way I would prevent it is by not using formulas like:
=IF(E:E="","",(TODAY()-E:E)/365)

Although excel accepts this style and figures out what you want, I find using
the more explicit:

=if(e1="","",(today()-e1)/365)

ps.

You may want to read Chip Pearson's notes for =datedif():
http://www.cpearson.com/excel/datedif.htm

Tendresse wrote:

Hi all,
I have a workbook with 2 identical worksheets. When data in one row in the
1st worksheet is no longer needed, I cut the whole row and paste it in the
second worksheet. I noticed that when i do so, the formulas change. For
example, i have a formula in the 1st worksheet to calculate a person's age:

=IF(E:E="","",(TODAY()-E:E)/365)

When i cut the row that contains this formula and paste it - say in row 30
of the second worksheet, the formula automatically turns into:

=IF(E$30:E$30="","",(TODAY()-E$30:E$30)/365)

The result of both formulas is the same. However, an a few occasions, the
formula changed to something like:

=IF(E$26:E$27="","",(TODAY()-E$26:E$27)/365) (which refers to 2 different
rows: 26 and 27)

The result for that last formula however was #VALUE!

I would like to know why this happens and how i can possibly prevent it.
Many thanks

--

Dave Peterson