Using the $ in cell value (e.g. $A$1)

[Jay]

Hi all,

First, I have to admit I'm a complete moron. I just found out about using
the $ symbol in a cell to set the cell location when copy/pasting. Brilliant
idea, wish I'd figured it out sooner.

My problem is, I can't find any information on this. Probably because my
search terms aren't appropriate. I'm having a complete brain fart here.

So I'm hoping someone can tell me what this is called. Also, is there a way
to set the cell value from 'Sheet1'!A1 to 'Sheet1'!$A$1? A function key? A
co-worker says they saw someone do it, but can't remember what they used. I
have a couple of sheets I want to apply this to, and I don't want to retype
every value.

Any help is appreciated.

Jay

[Excelenator]

f4 will cycle through the various stages of cell references. Just edit the cell, highlight the portion of the formula you want to "lock" and hit F4.

[Dave O]

Hi, Jay-
The "cell anchor" idea does take some getting used to: I have to figure
it out every time, pretty much. Here's a summary:
A1: no anchors applied
$A1: column reference locked. If you copy and paste this formula into
another cell, it will always reference column A, but the row reference
will change accordingly.
A$1: row reference locked. If you copy and paste this formula into
another cell, it will always reference row 1, but the column reference
will change accordingly.
$A$1: fully locked. If you copy and paste this formula it will always
reference A1, regardless of where you paste it.

When you're entering a formula- that is to say, you're in Edit mode
typing a formula into the formula bar or clicking to make cell
references, you can press the F4 key to apply cell anchors. If you've
already entered the formula and want to anchor a cell reference, land
the cell pointer on your cell and either a) press F2 to edit or b)
click on the formula bar. Place the cursor inside the cell reference
you want to anchor, or highlight it, and press F4 until the $ signs
appear in the right place.
Press F4 once: apply both anchors
F4 again: apply row anchor only
F4 again: apply column anchor only
F4 again: remove anchors

Dave O

[David Biddulph]

"Jay" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> First, I have to admit I'm a complete moron. I just found out about using
> the $ symbol in a cell to set the cell location when copy/pasting.
> Brilliant
> idea, wish I'd figured it out sooner.
>
> My problem is, I can't find any information on this. Probably because my
> search terms aren't appropriate. I'm having a complete brain fart here.
>
> So I'm hoping someone can tell me what this is called.

Absolute addressing, as distinct from relative addressing.
--
David Biddulph

[Jay]

Thanks to all three of you. Got all my questions answered.

"David Biddulph" wrote:

> "Jay" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> >
> > First, I have to admit I'm a complete moron. I just found out about using
> > the $ symbol in a cell to set the cell location when copy/pasting.
> > Brilliant
> > idea, wish I'd figured it out sooner.
> >
> > My problem is, I can't find any information on this. Probably because my
> > search terms aren't appropriate. I'm having a complete brain fart here.
> >
> > So I'm hoping someone can tell me what this is called.
>
> Absolute addressing, as distinct from relative addressing.
> --
> David Biddulph
>
>
>