copying and controling a formula cell to cell

  • Thread starter Thread starter kevin
  • Start date Start date
K

kevin

I have a long repetitive formula that refers to other
cells and I want to copy this formula over several
seqential cells. As I copy from cell to cell the
reference letters of the formula advance. i.e. A1 becomes
B1 then C1 etc. I have parts of the formula I would like
to refer to the same cell i.e. A3 repeatedly. Is there a
way to lock a part of the formula and not the other parts
or have excel recognize that part of the formula cell
letter reference should not advance to new letters as the
formula is copied over many cells?

Thanks
Kevin
 
Hi Kevin,

One way is to define names for the cells you want to remain the same.
But also, Excel recognizes absolute and realtive addresses. A1 is relative,
$A$1 is absolute, A$1 and $A1 are obvious mixes.
If you edit your formula, place the cursor in the address and hit F4
(repeatedly) to get the right mix.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi Kevin
use the '$' sign to create abolsute reference (instead of relative
references). So in your case you may replace A3 with $A$3

Frank
 
Hi Kevin!

If you use =A1 then copying down one cell will cause the formula to
refer to A2. Copying across will cause it to refer to B1.

If you use =$A$1 then it will refer to A1 wherever it is copied to.
The $ before the column letter and row number "locks" the formula on
the column and letter.

If you use =A$1 then when you copy down one cell it will still refer
to A1. However, when you copy across one cell it will refer to B1. The
$ before the row "locks" the formula on the row but with no $ before
the column, the column is allowed to vary with copying.

Similarly, if you use =$A1 then when you copy down one cell it will
refer to A2. However, when you copy across it will refer to A1

These four options can be cycled through during entry or editing
formulas by pressing F4.

See:

Chip Pearson:
http://www.cpearson.com/excel/relative.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks
-----Original Message-----
Hi Kevin
use the '$' sign to create abolsute reference (instead of relative
references). So in your case you may replace A3 with $A$3

Frank



.
Click to expand...
 
thanks very much

kevin
-----Original Message-----
Hi Kevin!

If you use =A1 then copying down one cell will cause the formula to
refer to A2. Copying across will cause it to refer to B1.

If you use =$A$1 then it will refer to A1 wherever it is copied to.
The $ before the column letter and row number "locks" the formula on
the column and letter.

If you use =A$1 then when you copy down one cell it will still refer
to A1. However, when you copy across one cell it will refer to B1. The
$ before the row "locks" the formula on the row but with no $ before
the column, the column is allowed to vary with copying.

Similarly, if you use =$A1 then when you copy down one cell it will
refer to A2. However, when you copy across it will refer to A1

These four options can be cycled through during entry or editing
formulas by pressing F4.

See:

Chip Pearson:
http://www.cpearson.com/excel/relative.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.



.
Click to expand...
 
Back
Top