Moving data in Excel

By hazcon ·
I have an excel spreadsheet template with multiple formulas. Users do not access the formulas but they have a horrible habit of dragging the bottom row of data to the top and then filling down when starting a new form. This throws everything out as now the last formula is referring to the top line and there are no formulas for the following data due to the drag and fill.
Is there a way to make the formulae always refer to a particular cell even if someone "drags" that cell somewhere else?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

re: moving data in Excel

by ThumbsUp2 In reply to Moving data in Excel

Personally, I would protect the cells so they can't do that to you. However, since you asked.... yes there is a way.

When you put a formula in a cell referencing another cell, use the $ in the cell reference. For example, fill a small sheet with this data:

A1 = 0
A2 = 0
A3 = 1
A4 = 0
A5 = 0

In cells B1 through C5, put any numbers so we can total them across in column D.

In cell D1, you place the formula:

When that cell is "dragged" down, the cell references change to the new row references...
(in D2) =IF(A2=0,B2+C2,"nada")
(in D3) =IF(A3=0,B3+C3,"nada")
etc..... all the way down.

However, if you use an absolute reference and place THIS formula in cell E1 and drag it down....

It will change to:
(in D2) =IF($A$1=0,B2+C2,"nada")
(in D3) =IF($A$1=0,B3+C3,"nada")
etc... all the way down.

Now, try copying cell E1 to cell F10. You'll see that the $A$1 remains unchanged but the reference to adding column B and C changed to column C and D, both row 10. To prevent this, you simply use 1 $ sign in front of the column designation...

Change cell E1 to read:
then drag it down.

You see the values in column E didn't change, which is good!

NOW... copy cell E1 and paste it in cell F10. BINGO!!! Even though there is nothing in column B or C to add, the column reference remains intact adding column B and C and NOT columns C and D like it did before.

The same can be done to prevent the rows from changing ....

In cell F1, type this:
now drag it down. Note, the formulas do NOT change one little bit. It copies exactly... going down that is... now drag F1 to the right and see what happens.

So, you just have to play with your formulas... if you don't want the cell reference to change even if the cell is copied to another locations... use the $

Collapse -

You misunderstood my problem.

by hazcon In reply to re: moving data in Excel

I understand about the $ in formulas but it is not the formual that is dragged. in my spreadsheet the formulas are protected and they can't access those cells. It is the data reference cell that gets dragged.
In your example, if someone drags A1 to A9, the formula in D1 changes from =IF(A1=0,B1+C1,"nada") to =IF(A9=0,B1+C1,"nada"). It is this change that I want to stop.

Collapse -

Yes, I misunderstood

by ThumbsUp2 In reply to You misunderstood my prob ...

I thought they were dragging the cross down (copying down). I didn't understand they were actually MOVING the cell from the bottom to the top (not copying).

Yes, that changes the cell reference in D1 to follow the referenced cell even IF the formula in D1 reads =IF($A$1,B1+C1,"nada") before they moved A1 to A9.

Let me look to see what I can find. BRB!

Collapse -

Found a SOLUTION!!! Well...... sort of.....

by ThumbsUp2 In reply to Yes, I misunderstood

Ok, this is going to be a lot of work, but it will fix the problem!!!! If they move the cell, it will break the formula. I also used an offset to make sure the cell reference wouldn't change.

fill cells as follows:

A1 = 0
B1 = 2
C1 = 5
D1 type this formula:


Now, if they drag a cell (move it), it becomes blank and prints "nada" in cell D1.

Not perfect, but it worked. If they move the cell, they break the spreadsheet..... Tsk! Tsk! Tsk!.... maybe they'll learn to keep their hands off the danged thing and quit moving the cell!

Collapse -

How about

by LocoLobo In reply to Moving data in Excel

protecting the cells so they can't move them. Are you using Excel 2003?

Collapse -

I was just going to say that......

by ThumbsUp2 In reply to How about

But, you beat me to it. When protecting the sheet, you can choose to not allow that cell to even be "selected", so the CAN'T drag it. However, that doesn't work if they actually NEED to put data into the cell itself. So, I'll go search to see what else I can find to prevent the cell reference from changing, short of training the users to keep their danged hands off of it, of course.

Related Discussions

Related Forums