D
DOUG ECKERT
What is the difference between the absolute cell reference format "$A$1" and
a cell reference formatted as "$A1"?
a cell reference formatted as "$A1"?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Bundy
No matter where you drag or copy $A$1 it will not change the reference. The $
holds the character beside it static, since there is not one by the 1 in $A1
then when you copy or move around it will Stay A but the 1 will change. Also
putting a A$1 will allow the column to change, but it will always reference
row 1.
holds the character beside it static, since there is not one by the 1 in $A1
then when you copy or move around it will Stay A but the 1 will change. Also
putting a A$1 will allow the column to change, but it will always reference
row 1.
D
DOUG ECKERT
Thanks, John!
DOUG
DOUG
John Bundy said:No matter where you drag or copy $A$1 it will not change the reference. The $
holds the character beside it static, since there is not one by the 1 in $A1
then when you copy or move around it will Stay A but the 1 will change. Also
putting a A$1 will allow the column to change, but it will always reference
row 1.
C
Chip Pearson
Just to add to John's correct response... Even if you use a formula like
$A10 or $A$10, Excel will change to row and/or column references, regardless
of the '$' characters, when a row and/or column is inserted. For example,
the function =$C$10 will be changed to =$C$11 if a row within 1:10 is
inserted. To completely prevent Excel from changing the cell reference
under any circumstance, you can use the INDIRECT function. E.g.,
=SUM(INDIRECT("A1:A10"))
The will *always* reference A1:A10 regardless of if and where rows/columns
might inserted/deleted. This works because Excel treats the "A1:A10" as a
simple text string, not a cell reference.
--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
$A10 or $A$10, Excel will change to row and/or column references, regardless
of the '$' characters, when a row and/or column is inserted. For example,
the function =$C$10 will be changed to =$C$11 if a row within 1:10 is
inserted. To completely prevent Excel from changing the cell reference
under any circumstance, you can use the INDIRECT function. E.g.,
=SUM(INDIRECT("A1:A10"))
The will *always* reference A1:A10 regardless of if and where rows/columns
might inserted/deleted. This works because Excel treats the "A1:A10" as a
simple text string, not a cell reference.
--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
D
DOUG ECKERT
Chip Pearson said:Just to add to John's correct response... Even if you use a formula like
$A10 or $A$10, Excel will change to row and/or column references, regardless
of the '$' characters, when a row and/or column is inserted. For example,
the function =$C$10 will be changed to =$C$11 if a row within 1:10 is
inserted. To completely prevent Excel from changing the cell reference
under any circumstance, you can use the INDIRECT function. E.g.,
=SUM(INDIRECT("A1:A10"))
The will *always* reference A1:A10 regardless of if and where rows/columns
might inserted/deleted. This works because Excel treats the "A1:A10" as a
simple text string, not a cell reference.
--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)