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

  • Thread starter Thread starter CompleteNewb
  • Start date Start date
C

CompleteNewb

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.
 
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?
Click to expand...

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
 
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
 
=INDIRECT("$A$1") does what you want.
Note the quotation marks.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"CompleteNewb"
<[email protected]>
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.
 
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.
--
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...
 
Fri, 15 Jun 2007 22:42:04 -0400 from CompleteNewb
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.
Click to expand...

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.
 
Back
Top