Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting #REF1 because re-pasted a replacement worksheet | Excel Discussion (Misc queries) | |||
Same formula referencing same cells returns incorrect results, randomly, when pasted into new worksheet | Excel Worksheet Functions | |||
Password protection of worksheet when copied and pasted. | Excel Worksheet Functions | |||
How do I protect a worksheet from being copied & pasted? | Excel Discussion (Misc queries) | |||
Validating data pasted into worksheet | Excel Discussion (Misc queries) |