Absolute positioning not working; am I thinking about it wrong?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CompleteNewb - 16 Jun 2007 03:40 GMT

I was under the impression that if one used the "$" in a cell reference,
that would mean that whether or not columns and/or rows are inserted, the
reference would remain at the ORIGINAL spot. So, for instance, if I put in
cell C3 the following:

=$A$1

Then C3 would show what's in cell A1. I THOUGHT that if I then inserted a
column to the left of A, C3 would STILL have:

=$A$1

It doesn't, however. When I insert the column, C3's formula changes to:

=$B$1

What I find especially confusing about this is that the behavior is
identical when I DON'T use the "$". If Cell C3 has:

=A1

then inserting the column before A would change C3 to:

=B1

So what is the benefit of using $? How can I make it so that regardless of
rows and columns being inserted, a cell will always use the contents of
what's in column A, row 1?

Using Excel 2002, but have seen same behavior in 2003 and XP.

Any help appreciated, and thanks.

Reply to this Message

T. Valko - 16 Jun 2007 04:07 GMT

>How can I make it so that regardless of rows and columns being inserted, a
>cell will always use the contents of what's in column A, row 1?

Try this:

=INDIRECT("A1")

That will *always* refer to cell A1.

The $ signs are only good for "locking" the reference when copying a
formula. As you've discovered, inserting rows/columns will change the
references.

Bif

>I was under the impression that if one used the "$" in a cell reference,
>that would mean that whether or not columns and/or rows are inserted, the
[quoted text clipped - 28 lines]
>
> Any help appreciated, and thanks.

Reply to this Message

Ron Coderre - 16 Jun 2007 04:12 GMT

Absolute references, like =$A$1, are at their best when copying formulas.

However, =$A$1 always refers to that cell....so if it moves due to the
addition of rows or columns...the formula refers to the new location.

Try something like this:
=INDEX($1:$65536,1,1)
or
=INDIRECT("A1")

Note, though, INDEX only recalculates when it needs to. INDIRECT is
volatile (meaning it recalculates whenever any cell in the workbook
recalculates). That can be a problem in some circumstances.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> I was under the impression that if one used the "$" in a cell reference,
> that would mean that whether or not columns and/or rows are inserted, the
[quoted text clipped - 28 lines]
>
> Any help appreciated, and thanks.

Reply to this Message

Jim Cone - 16 Jun 2007 04:13 GMT

=INDIRECT("$A$1") does what you want.
Note the quotation marks.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"CompleteNewb"
<CompleteNewb@comcast.net>
wrote in message
I was under the impression that if one used the "$" in a cell reference,
that would mean that whether or not columns and/or rows are inserted, the
reference would remain at the ORIGINAL spot. So, for instance, if I put in
cell C3 the following:
=$A$1
Then C3 would show what's in cell A1. I THOUGHT that if I then inserted a
column to the left of A, C3 would STILL have:
=$A$1
It doesn't, however. When I insert the column, C3's formula changes to:
=$B$1
What I find especially confusing about this is that the behavior is
identical when I DON'T use the "$". If Cell C3 has:
=A1
then inserting the column before A would change C3 to:
=B1
So what is the benefit of using $? How can I make it so that regardless of
rows and columns being inserted, a cell will always use the contents of
what's in column A, row 1?
Using Excel 2002, but have seen same behavior in 2003 and XP.
Any help appreciated, and thanks.

Reply to this Message

Earl Kiosterud - 16 Jun 2007 04:20 GMT

Newb,

The absolute symbol, $, means one thing only -- the cell reference won't be changed when
it's copied. But when the cell gets moved, the reference moves with it. $ or no $. This
is true if the move is a Cut/Paste, an edge-drag move, or the result of inserting or
deleting rows or columns.

For a cell reference to not be affected by inserts and deletes, you can use INDIRECT:

=INDIRECT("A2") The quote marks are necessary.

By the way, the reason the A2 doesn't get adjusted as a result of a move is that it's not
really a cell reference -- it's really text. A string.

Signature

Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

>I was under the impression that if one used the "$" in a cell reference, that would mean
>that whether or not columns and/or rows are inserted, the reference would remain at the
[quoted text clipped - 26 lines]
>
> Any help appreciated, and thanks.

Reply to this Message

Stan Brown - 16 Jun 2007 15:40 GMT

Fri, 15 Jun 2007 22:42:04 -0400 from CompleteNewb
<CompleteNewb@comcast.net>:

> I was under the impression that if one used the "$" in a cell reference,
> that would mean that whether or not columns and/or rows are inserted, the
> reference would remain at the ORIGINAL spot.

Absolute references are about what happens when you copy/paste, or
when you use the little fill handle to drag a formula to fill a
rectangular range.

When you insert rows or columns, all references relative and absolute
are automatically adjusted to point to what they used to point to,
but now in its new location.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

Rate this thread: