flexible formulas

cpc

New Member
Joined
Mar 14, 2002
Messages
38
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)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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?
 
Upvote 0
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) ?
 
Upvote 0
Aladin,

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..
 
Upvote 0
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?

Aladin
 
Upvote 0
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)

Is this or a variant of possible in your spreadsheet?

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.
 
Upvote 0
Aladin,

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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top