How to prevent any changes made to formulas from moving reference cells?

[Morrigan]

Let's say I have a number in each of the cell A1, A2, and A3. Cell A
is just the sum of them. (ie A4=sum(A1:A3))
Now if I move all the reference cells(A1,A2,and A3) down one row to B1
B2, and B3, the formula in A4 will now become "=sum(B1:B3)". Excel i
too smart to does it for you automatically. However, I do NOT wan
this to happen; I want my formula remain unchange. Is there a way t
do it?

Thank you very much

 

[Max]

Try in say, A4:

=SUM(OFFSET(INDIRECT(CHAR(COLUMN()+96)&"1"),,,ROW()-1))

This formula will *fix* and give the sum of all the col cells in the rows
above it.

Moving the "original" cells A1:A3 to say B1:B3 will *not* affect the formula
in A4.

It will continue to sum only the values in A1:A3

 

[Dave Peterson]

Something like this might work:

=SUM(INDIRECT("a1:a3"))

If you want it to always point at A1:A3.

 

[Guest]

Dont cut and paste just copy paste and delete the previous entry

 

[Morrigan]

Now I run into another problem that is I won't be able to generat
similar formula to the next cell.

When I try to generate a similar formula to B4. Excel does not kno
how to update the reference cells to B1,B2,B3.(ie
B4=sum(indirect("A1:A3"))) Similarly, C4 read
"=sum(indirect("A1:A3"))". Is there a way to solve this problem?


Thank you again

 

[Morrigan]

The problem is I am not the one doing the data entry. I have no contro
over that, but thank you anyway.

 

[Max]

The suggestion I gave earlier will allow you to sum all rows in the col
above,
and to copy right across up to 26 cols at one go.

Put in say, A4: =SUM(OFFSET(INDIRECT(CHAR(COLUMN()+96)&"1"),,,ROW()-1))

This formula will *fix* and give the sum of all the col cells in the rows
above it.

Moving the "original" cells A1:A3 to say B1:B3 will *not* affect the formula
in A4.

It will continue to sum only the values in A1:A3

Copy across up to col Z, to give sum of all the rows in the cols above the
formula

---

If you need to extend beyond col Z, e.g. for cols AA to AZ,

Put the amended formula below in say, AA4:

=SUM(OFFSET(INDIRECT("A"&CHAR(COLUMN()+70)&"1"),,,ROW()-1))

Copy across from AA4 to AZ4

--
For cols BA to BZ, just change accordingly to:

In BA4:

=SUM(OFFSET(INDIRECT("B"&CHAR(COLUMN()+44)&"1"),,,ROW()-1))

Copy across from BA4 to BZ4

--
For each block of 26 cols beyond cols A-Z, i.e. cols AA-ZZ, BA-BZ, CA-CZ,
etc:
a. change the extra header from "A" to "B" to "C" and so on, and
b. adjust the numerical values "96", "70", "44" by subtracting 26 from the
previous block

 

[Max]

Maybe see my reply given to you earlier
and the follow-through [pasted below] in the other thread?

-------------
The suggestion I gave earlier will allow you to sum all rows in the col
above, and to copy right across up to 26 cols at one go.

Put in say, A4

: =SUM(OFFSET(INDIRECT(CHAR(COLUMN()+96)&"1"),,,ROW()-1))

This formula will *fix* and give the sum of all the col cells in the rows
above it.

Moving the "original" cells A1:A3 to say B1:B3 will *not* affect the formula
in A4.

It will continue to sum only the values in A1:A3

Copy across up to col Z, to give sum of all the rows in the cols above the
formula

---

If you need to extend beyond col Z, e.g. for cols AA to AZ,

Put the amended formula below in say, AA4:

=SUM(OFFSET(INDIRECT("A"&CHAR(COLUMN()+70)&"1"),,,ROW()-1))

Copy across from AA4 to AZ4

--
For cols BA to BZ, just change accordingly to:

In BA4:

=SUM(OFFSET(INDIRECT("B"&CHAR(COLUMN()+44)&"1"),,,ROW()-1))

Copy across from BA4 to BZ4

--
For each block of 26 cols beyond cols A-Z, i.e. cols AA-ZZ, BA-BZ, CA-CZ,
etc:
a. change the extra header from "A" to "B" to "C" and so on, and
b. adjust the numerical values "96", "70", "44" by subtracting 26 from the
previous block

 

[Morrigan]

Thank you so much for your reply, I finally just figured out a way tha
will fit in my application. Just for my own interest, how do I modif
your formula so that it will sum all the cells in the same row instea
of in the same column?


Thank you

 

[Max]

.. Just for my own interest, how do I modify

your formula so that it will sum all the cells in the same row instead
of in the same column?

Simplest might be just to put in a cell
which is *outside* the row being summed

For example, put in A4: =SUM(1:1)

Copying A4 down to A6 will increment the row summed accordingly

viz. in A5: =SUM(2:2), in A6: =SUM(3:3), etc