VBA Cell Ref "$A$1" versus "$A1"
 

What is the difference between the absolute cell reference format "$A$1" and
a cell reference formatted as "$A1"?

VBA Cell Ref "$A$1" versus "$A1"
 

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"DOUG ECKERT" wrote:

What is the difference between the absolute cell reference format "$A$1" and
a cell reference formatted as "$A1"?

VBA Cell Ref "$A$1" versus "$A1"
 

Thanks, John!

DOUG

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"DOUG ECKERT" wrote:

What is the difference between the absolute cell reference format "$A$1" and
a cell reference formatted as "$A1"?

VBA Cell Ref "$A$1" versus "$A1"
 

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)


"John Bundy" (remove) wrote in message
...
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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"DOUG ECKERT" wrote:

What is the difference between the absolute cell reference format "$A$1"
and
a cell reference formatted as "$A1"?

VBA Cell Ref "$A$1" versus "$A1"
 

"Chip Pearson" wrote:

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)


"John Bundy" (remove) wrote in message
...
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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"DOUG ECKERT" wrote:

What is the difference between the absolute cell reference format "$A$1"
and
a cell reference formatted as "$A1"?