Absolute reference to column, row

[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

 

[Max]

Try replacing C3 in the formula with INDIRECT("C3")

This should fix it.

 

[Siberian Tiger]

Try replacing C3 in the formula with INDIRECT("C3")

This should fix it.

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

 

[Max]

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
----

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

 

[Lady Layla]

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

 

[Siberian Tiger]

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]))

 

[Max]

Pleased to hear that, Mike !
Thanks for the feedback