Dragging a Cell Changes Formula

[JanetW]

I am creating a spreadsheet which will be protected with a table of
cells unlocked for the user to fill in. There are formulas which
reference the unlocked cells. Is there anyway to prevent these formulas
from changing if the user drags (or cut-and-pastes) the unlocked cell
(ie, if they type data in the wrong cell and then drag that data to the
correct cell, any formula reference these two cells gets modified)?

Thanks!

 

[John Wilson]

Janet,

Make the formulas "absolute"

=A1 will adjust when it's moved or dragged anywhere.
=$A1 will "lock" the column
=A$1 will "lock" the row
=$A$1 will lock both the row and column.

John

 

[Tim C]

I am creating a spreadsheet which will be protected with a table of
cells unlocked for the user to fill in. There are formulas which
reference the unlocked cells. Is there anyway to prevent these formulas
from changing if the user drags (or cut-and-pastes) the unlocked cell
(ie, if they type data in the wrong cell and then drag that data to the
correct cell, any formula reference these two cells gets modified)?

Thanks!

Tools/Options/Edit/Edit directly in cell

This isn't exactly what you asked for, but it will prevent dragging and
dropping of cells from being used at all.

Tim C

 

[JanetW]

John,

A misunderstanding...the cell with the formula isn't being moved. That
cell is locked. The cell being referenced to (by a formula in another
cell) could potentiall be moved because it's unlocked. But thanks
anyway!

Tim,

I'm assuming you meant:

Tools/Options/Edit/Allow cell drag and drop

But the problem with that is that it appears to be an application
specific option, not a file specific one (I'm always curious why MS put
both types of options in one dialog box without any indication of which
is which!). Therefore, everyone accessing the file would need to set
this option...and then it would apply to ALL their Excel files, not
just this one.

 

[RagDyer]

If you want to go through the trouble, you could use INDIRECT.

If your formula is:
=A1*B1
You could, for example, enter "A1" in F1 and "B1" in G1 (no quotes), and
then change the formula to:
=INDIRECT(F1)*INDIRECT(G1)

(I know there's a proper syntax so that you can enter Indirect only once, I
just can't remember it now.)

HTH,

RD

 

[JanetW]

Yes, it looks like INDIRECT is what I was looking for. The help even
says you could put the text directly in the INDIRECT function. So to
simplify your example:

INDIRECT("A1")*INDIRECT("B1")

Then you can skip the intermediate cells. Unfortunately, even if I had
know this trick before I started, it would have been extremely
impractical to use it on my current project (too complex as it is!).

But thanks for pointing me in the right direction. I will definitely be
using this on future (smaller) projects!

 

[wim]

Hi Janet,

Did you find a solution for this problem after all ?
I'm having the same problem.
Help would be much appreciated.

tnx

Wim

 

[JanetW]

The INDIRECT fuction is definitely what I was looking for, but for my
current project, especially for how far along it is, is far too complex
to go back and rewrite all the formulas to use this as a way to
reference cells within formulas. But it appears to be the only way to
provide an absolute reference to a cell that will persist through
either cut-n-paste or drag-n-drop of the referenced cell.

One thing I plan on experimenting with on the next project is how
INDIRECT can be efficiently used in situations when, for example, you
are filling formulas down a column. I think you should be able to use
INDIRECT along with ROW, COLUMN, and INDEX functions to create a
formula with absolute cell location references, but that will also
adjust properly as you fill the formula to adjacent cells.

As a simple example, if I wanted to add a number in column A to a
number in column B, and then fill down the result in column C, you
could write in C1:

=INDIRECT("A"&ROW())+INDIRECT("B"&ROW())

When you fill down this formula, it will still add the numbers from the
current row, but the formula would also stay intact if you drag-n-drop
a value in A or B to a new row.

Hope that helps!

 

[David McRitchie]

See if this example is of use to you
Build TOC Another Approach (use of INDIRECT)
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
-
HTH,
David McRitchie, Microsoft MVP - Excel