Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
Dear NG:
I'm curious about the purpose of the "$" in formulas when referring to cells. For example Sheet1!$A$1 I couldn't find anything about it in Excel or VBA help. Are there advantages or disadvantages to using the "$"? Should it be used always or are there times when it should not be used? Just curious. Thanks, -Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
By putting a $ symbol in front of cell references, you convert the
reference from a relative address to an absolute address, which means that when you copy a formula in a cell which contains absolute references it will not change. If effectively fixes the row or column part (or both) of the cell reference. You can have: A1 - relative address, gets changed when copied across or down $A1 - fixed column address, only the 1 changes when copied down, $A remains the same when copied across A$1 - fixed row address, only the A changes when copied across, $1 remains the same when copied down $A$1 - full absolute address, neither part changes when copied across or down. Look in Excel Help for Relative/Absolute references for more details. Hope this helps. Pete On Aug 16, 3:56 pm, "Kevin" wrote: Dear NG: I'm curious about the purpose of the "$" in formulas when referring to cells. For example Sheet1!$A$1 I couldn't find anything about it in Excel or VBA help. Are there advantages or disadvantages to using the "$"? Should it be used always or are there times when it should not be used? Just curious. Thanks, -Kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
There are two types of cell referencing..........relative and absolute.
The $ sign designates an absolute reference for a row or column In the case of $A$1 both row and column are absolute. See help on "about cell and range references" to gain a better understanding of the two types and when to use them. Gord Dibben MS Excel MVP On Thu, 16 Aug 2007 10:56:48 -0400, "Kevin" wrote: Dear NG: I'm curious about the purpose of the "$" in formulas when referring to cells. For example Sheet1!$A$1 I couldn't find anything about it in Excel or VBA help. Are there advantages or disadvantages to using the "$"? Should it be used always or are there times when it should not be used? Just curious. Thanks, -Kevin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
The dollar signs don't matter -- unless or until you copy the formula
to another cell. Use a dollar sign before a row or column to keep it from changing. When editing your formula, you can press F4 to cycle through the four possible combinations. I couldn't find anything about it in Excel or VBA help. Jeez, it's only seven levels deep. What have you been doing all day? Help Microsoft Excel Help Table of Contents Working with Data Formulas Creating Formulas About Formulas The difference between relative and absolute references <---- Thar she blows! - David |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
$'s in Cell Ref are there primarily for any FUTURE COPYING -- you might do of
the cell. The $ makes a coordinate FIXED so that when it is copied IT DOES NOT CHANGE !! It's called or refered to as ABSOLUTE ADDRESSING (versus RELATIVE ADDRESSING)and can be for a row only ($A1), a column only (A$1) or both ($A$1). HTH "Kevin" wrote: Dear NG: I'm curious about the purpose of the "$" in formulas when referring to cells. For example Sheet1!$A$1 I couldn't find anything about it in Excel or VBA help. Are there advantages or disadvantages to using the "$"? Should it be used always or are there times when it should not be used? Just curious. Thanks, -Kevin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
Pete_UK
Thanks Pete. I've copied your information to a file for future reference. Thanks again. -Kevin "Pete_UK" wrote in message oups.com... By putting a $ symbol in front of cell references, you convert the reference from a relative address to an absolute address, which means that when you copy a formula in a cell which contains absolute references it will not change. If effectively fixes the row or column part (or both) of the cell reference. You can have: A1 - relative address, gets changed when copied across or down $A1 - fixed column address, only the 1 changes when copied down, $A remains the same when copied across A$1 - fixed row address, only the A changes when copied across, $1 remains the same when copied down $A$1 - full absolute address, neither part changes when copied across or down. Look in Excel Help for Relative/Absolute references for more details. Hope this helps. Pete On Aug 16, 3:56 pm, "Kevin" wrote: Dear NG: I'm curious about the purpose of the "$" in formulas when referring to cells. For example Sheet1!$A$1 I couldn't find anything about it in Excel or VBA help. Are there advantages or disadvantages to using the "$"? Should it be used always or are there times when it should not be used? Just curious. Thanks, -Kevin |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
Thanks to Gord Dibben, Jim May and David Hilberg.
It's a funny thing David, I never thought to look 7 levels deep. Silly me. Thanks again to all. -Kevin "Kevin" wrote in message ... Dear NG: I'm curious about the purpose of the "$" in formulas when referring to cells. For example Sheet1!$A$1 I couldn't find anything about it in Excel or VBA help. Are there advantages or disadvantages to using the "$"? Should it be used always or are there times when it should not be used? Just curious. Thanks, -Kevin |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Curious about "$" in formulas
You're welcome, but the information is there in Excel Help if you care
to look. Pete On Aug 17, 2:29 pm, "Kevin" wrote: Pete_UK Thanks Pete. I've copied your information to a file for future reference. Thanks again. -Kevin "Pete_UK" wrote in message oups.com... By putting a $ symbol in front of cell references, you convert the reference from a relative address to an absolute address, which means that when you copy a formula in a cell which contains absolute references it will not change. If effectively fixes the row or column part (or both) of the cell reference. You can have: A1 - relative address, gets changed when copied across or down $A1 - fixed column address, only the 1 changes when copied down, $A remains the same when copied across A$1 - fixed row address, only the A changes when copied across, $1 remains the same when copied down $A$1 - full absolute address, neither part changes when copied across or down. Look in Excel Help for Relative/Absolute references for more details. Hope this helps. Pete On Aug 16, 3:56 pm, "Kevin" wrote: Dear NG: I'm curious about the purpose of the "$" in formulas when referring to cells. For example Sheet1!$A$1 I couldn't find anything about it in Excel or VBA help. Are there advantages or disadvantages to using the "$"? Should it be used always or are there times when it should not be used? Just curious. Thanks, -Kevin- Hide quoted text - - Show quoted text - |
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 | |||
Combine logical formulas "if", "and", "or" | Excel Discussion (Misc queries) | |||
Combining formulas, "and" & "or" to verify content of multiple cel | 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 |