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

  • Thread starter Thread starter Morrigan
  • Start date Start date
M

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
 
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
 
Something like this might work:

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

If you want it to always point at A1:A3.
 
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
 
The problem is I am not the one doing the data entry. I have no contro
over that, but thank you anyway.
 
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
 
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
 
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
 
.. 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?
Click to expand...

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