Curious about "$" in formulas

[Kevin]

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

 

[Pete_UK]

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

 

[Gord Dibben]

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

 

[David Hilberg]

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

 

[Guest]

$'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]

Pete_UK
Thanks Pete. I've copied your information to a file for future reference.
Thanks again.
-Kevin

 

[Kevin]

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

 

[Pete_UK]

You're welcome, but the information is there in Excel Help if you care
to look.

Pete