Question about Cells, Insert, Delete, & Formulas

K

Kevin

I have a workbook consisting of two (locked) Report worksheets
and a number of worksheets where data is manually added, deleted,
inserted, and updated.

On my Report worksheets, I use formulas like:

= SUM(Jones!A4:A50)



The Problem:

When technician JONES edits the data on his JONES worksheet, he
commonly INSERTS cells, DELETES cells, and edits cells. This is
fine EXCEPT that JONES' changes show up in my formulae.

For example,

= SUM(Jones!A4:A50)

is changed

=SUM(Jones!A6:A33)


I don't want to force JONES (and others) into using locked cells.

Do I have any options?
 
M

Max

If you're looking to "lock-in" a certain range in all individual sheets,
something along these lines might help

In a summary sheet:

List the sheetnames across, say, in B1:C1: Jones, Peter
Enter the desired range into A2: A4:A50

Then just put in B2: =SUM(INDIRECT("'"&B$1&"'!"&$A2))
and copy B2 across to C2

B2 will return the same as: = SUM(Jones!A4:A50)
C2 will return the same as: = SUM(Peter!A4:A50)

And B2, C2 will continue to point to the range A4:A50
despite Jones' / Peter's actions on their sheets
 
K

Kevin

Max said:
If you're looking to "lock-in" a certain range in all individual sheets,
something along these lines might help

In a summary sheet:

List the sheetnames across, say, in B1:C1: Jones, Peter
Enter the desired range into A2: A4:A50

Then just put in B2: =SUM(INDIRECT("'"&B$1&"'!"&$A2))
and copy B2 across to C2

B2 will return the same as: = SUM(Jones!A4:A50)
C2 will return the same as: = SUM(Peter!A4:A50)

And B2, C2 will continue to point to the range A4:A50
despite Jones' / Peter's actions on their sheets
--
Rgds
Max
xl 97
---
Click to expand...

Thank you Max!

That's a great tip!
 
D

David McRitchie

Hi Kevin,
I see you've got an answer that you are happy with.

Since you are probably drawing in the sum of a column
without a total cell in that column you could possibly use
= SUM(Jones!A:A)
if the few cells that you are not interested in in that column
are text and not numeric values. I didn't try testing for time
but I think it would be faster than a SUM using INDIRECT with
an arbitrary block of cells.

I think you might want to look at two of my pages that
specifically address maintaining totals while inserting
or deleting rows including those immediately above
your totals.
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
http://www.mvps.org/dmcritchie/excel/offset.htm
 
Top