Thanks:  0
Likes:  0

1. Tough problem for me. Is it possible to have
a formula that is some what self adapting.
I will explain what I mean. I have a list
of numbers. cells a1-a9. cell a10 has the
formula =a1+a2+a3+a4+a5+a6+a7+a8+a9
Assume the formula must be written with each cell noted. The actually set up is with multiple pages, formulas, cells etc.

Problem. If I delete a line, say a4 the formula does not work. ref# is shown in a10
as well as in the formula itself. Is there a
way to create the formula so that if I delete
a line, the formula will simply calculate the
remaining cells.(=a1+a2+a3+a5+a6+a7+a8+a9)

2. =SUM(A1:A9)

3. This will not work. As I mentioned, each cell needs to be listed. If I then delete the line can the formulated cell automatically adjust for the missing cell?

4. On 2002-04-11 22:49, cpc wrote:
This will not work. As I mentioned, each cell needs to be listed. If I then delete the line can the formulated cell automatically adjust for the missing cell?
"delete the line" means deleting a row, is it not?

Suppose you delete row 4. Are you requiring that the formula that you use

=a1+a2+a3+a5+a6+a7+a8+a9

stays the same, without giving error?

And

=SUM(A1:A9)

does not change to

=SUM(A1:A8) ?

If I understand your comment, the answer is yes. Assume you can not use the formula
=sum(a1:a9) You must list each cell in the formula. ie a1+a2+a3 and so on. When you
delete a row, and the formula now has a ref#
instead of the a4 ie the deleted row, can the formula ignore this ref# and still calculate
the remaining cells listed in the formula..

6. Assume you can not use the formula
=sum(a1:a9) You must list each cell in the formula. ie a1+a2+a3 and so on. When you
delete a row, and the formula now has a ref#
instead of the a4 ie the deleted row, can the formula ignore this ref# and still calculate the remaining cells listed in the formula.

No. But, why do you need to sum a range using the + operator that requires its operands to be explicitly listed?

7. I understand exactly what you mean. I have a spreadsheet littered with this type of problem.

I was able to get each page looking exactly the same and use the following formula:

=sum(sheet1:sheet3!A1)

I do not beleive that using the "+" operator is allows a dependant to be deleted without giving a #ref error. However, similar formulas that will. I'm still pondering another solution.

This type of formula needs to be used because in actuality the formula may pick up every other cell, not a1:a9, or a group of numbers and then a few single cells, or totals from
say 25 pages, when I want to delete say the last four pages. This maybe another way of looking at the problem. I have lists of numbers as described, and a total. (the formula in question) on say 49 pages, with page 50 being a grand total of all 49 pages.
If I delete say pages 40-49, asuume they are not being used, the formula on page 50 no longer works. Unless I re - formulate the cell. Can this formula on page 50 be written to allow for the deletion of pages.

croweld89 look at the above. Do you think your idea will work here?

9. Let's divide by 10 pages...

Say sheets 1 - 4 are populated and sheet 5 gives a grand total summary of the previous pages. If (and the big if), you can make sheets 1 - 4 the same, then this will work.

For example, we put "5" in cell D4 on sheets 1 - 4. Then on sheet 5 in cell B11 (or whatever cell you need) place the formula:

=sum(sheet1:sheet4!D4)

This will allow you to delete all but one sheets without receiving an error. This requires all formulas on sheet 5 to pull data that are only in the same cell on all 4 sheets. Let me know if this is possible.

There should be an alternative combining the index/match formulas, however, I have not adepted this formula yet.

-Dave

10. I don't see any way around manually editing your formulas. If possible, build your worksheets so that you won't have to delete the references that your formulas are built around.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•