Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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"? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |