Absolute reference to column, row

  • Thread starter Thread starter Siberian Tiger
  • Start date Start date
S

Siberian Tiger

It seems like this should be easy enough, but I can't find it in Excel help.

I want to make an absolutely absolute reference to a certain cell (or row/column reference).

Let's say my formula points to C3. If someone moves C3 to E5, my formula "follows" that cell. I don't
want it to! I want it to continue pointing to (the now empty) C3. How do I do that?!

--Mike
 
Try replacing C3 in the formula with INDIRECT("C3")
This should fix it.
Click to expand...


Yes, this works. However, I need to copy the formula down 400 times, and the reference should always
be to the cell on that line. When I copy a formula with the "indirect" in it, the original reference is
preserved.

Example: I want cell C1 to add A1 and B1. I want to copy C1 down column C (so C5 adds A5 and B5). I
want to be able to move any cell in column A or B without effecting the formulas in column C. Indirect
accomplishes this, but I can't copy the formula down because it maintains the absolute reference to cells
A1 and B1.

Any way around this?

-Mike
 
You can use ROW() as the incrementer in INDIRECT() for copying down
(Use COLUMN() as the incrementer for copying across)

Taking your example:

If you have in C1:=SUM(A1:B1), and C1 is copied down col C

Replace the formula in C1 by:

=SUM(INDIRECT("A"&ROW()&":B"&ROW()))

Copy C1 down

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
Siberian Tiger said:
Yes, this works. However, I need to copy the formula down 400 times, and the reference should always
be to the cell on that line. When I copy a formula with the "indirect" in it, the original reference is
preserved.

Example: I want cell C1 to add A1 and B1. I want to copy C1 down column C (so C5 adds A5 and B5). I
want to be able to move any cell in column A or B without effecting the formulas in column C. Indirect
accomplishes this, but I can't copy the formula down because it maintains
Click to expand...
the absolute reference to cells
 
In the original formula cell, place the cursor in the cell reference you want to
make absolute, then hit F4 until you get $C$3. When you copy the formula, this
should stay absolute


: It seems like this should be easy enough, but I can't find it in Excel help.
:
: I want to make an absolutely absolute reference to a certain cell (or
row/column reference).
:
: Let's say my formula points to C3. If someone moves C3 to E5, my formula
"follows" that cell. I don't
: want it to! I want it to continue pointing to (the now empty) C3. How do I do
that?!
:
: --Mike
 
Hi, Max,

Big thanks!!! It works! Here's my original formula, and the second line is my new and improved formula.

=$K$2-SUM($F$3:F8)+SUM($G$3:G8)

=$K$2-SUM($F$2:INDIRECT("F"&ROW()&""))+SUM($G$2:INDIRECT("G"&ROW()&"")))

K2 is my starting balance. I subtract expenses recorded in F3 - F8, and add deposits recorded in G3 -
G8. With this formula, I get a running balance. If a user moves or deletes any transactions or rows, the
formulas aren't messed up. I can also copy the formula down and it works perfect.

Thanks again for giving an amateur a hand with this!

Mike

(P.S. For cosmetic purposes, I also added the following: So a balance only appears in rows where
transctions are entered, I preface the formula above with: =IF(INDIRECT("G"&ROW()&"")+INDIRECT
("F"&ROW()&"")=0,"",[formula above]))
 
Back
Top