[thelist] MS Excel Question

Brett Stinson brettastinson at comcast.net
Mon Aug 25 20:08:57 CDT 2003


Casey:
  What you are referring to is the difference between absolute and relative
references.
Straight from the excel help, this should clear it up for you:

The difference between relative and absolute references
Relative references   When you create a formula, references to cells or
ranges are usually based on their position relative to the cell that
contains the formula. In the following example, cell B6 contains the formula
=A5; Microsoft Excel finds the value one cell above and one cell to the left
of B6. This is known as a relative reference.



When you copy a formula that uses relative references, Excel automatically
adjusts the references in the pasted formula to refer to different cells
relative to the position of the formula. In the following example, the
formula in cell B6, =A5, which is one cell above and to the left of B6, has
been copied to cell B7. Excel has adjusted the formula in cell B7 to =A6,
which refers to the cell that is one cell above and to the left of cell B7.



Absolute references   If you don't want Excel to adjust references when you
copy a formula to a different cell, use an absolute reference. For example,
if your formula multiplies cell A5 with cell C1 (=A5*C1) and you copy the
formula to another cell, Excel will adjust both references. You can create
an absolute reference to cell C1 by placing a dollar sign ($) before the
parts of the reference that do not change. To create an absolute reference
to cell C1, for example, add dollar signs to the formula as follows:

=A5*$C$1

Switching between relative and absolute references   If you created a
formula and want to change relative references to absolute (and vice versa),
select the cell that contains the formula. In the formula bar, select the
reference you want to change and then press F4. Each time you press F4,
Excel toggles through the combinations: absolute column and absolute row
(for example, $C$1); relative column and absolute row (C$1); absolute column
and relative row ($C1); and relative column and relative row (C1). For
example, if you select the address $A$1 in a formula and press F4, the
reference becomes A$1. Press F4 again and the reference becomes $A1, and so
on.


Hope this helps.

Brett Stinson
bstinson at programmer.net <mailto:bstinson at programmer.net>


-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org]On Behalf Of Casey Crookston
Sent: Monday, August 25, 2003 4:47 PM
To: thelist at lists.evolt.org
Subject: [thelist] MS Excel Question


When creating a formula in Excel you can, of course, reference other cells:

=b15+c15

If you copy and paste that cell, Excel is kind enough to change the cell
references relative to where you pasted.  But what if I want one of the cell
references to remain constant?  Example:

Original:    =B15+C15
Pasted:    =B16+C15
Pasted:    =B16+C15
Pasted:    =B16+C15
Pasted:    =E16+C15

How do you specify that C15 is to remain unchanged?

TIA,

Casey


--
* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !




More information about the thelist mailing list