flexible formulas

cpc · Apr 11th, 2002, 11:32 PM

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)

Aladin Akyurek · Apr 11th, 2002, 11:35 PM

=SUM(A1:A9)

cpc · Apr 11th, 2002, 11:49 PM

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?

Aladin Akyurek · Apr 12th, 2002, 12:01 AM

Quote:

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) ?

cpc · Apr 12th, 2002, 12:11 PM

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..

Aladin Akyurek · Apr 12th, 2002, 12:22 PM

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

croweld89 · Apr 12th, 2002, 12:36 PM

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.

cpc · Apr 12th, 2002, 01:00 PM

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?

croweld89 · Apr 12th, 2002, 01:17 PM

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

Scott R · Apr 12th, 2002, 02:30 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 11:32 PM	#1
cpc New Member Join Date: Mar 2002 Posts: 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)

Apr 11th, 2002, 11:35 PM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	=SUM(A1:A9)

Apr 11th, 2002, 11:49 PM	#3
cpc New Member Join Date: Mar 2002 Posts: 38	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?

Apr 12th, 2002, 12:11 PM	#5
cpc New Member Join Date: Mar 2002 Posts: 38	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..

Apr 12th, 2002, 12:22 PM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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

Apr 12th, 2002, 12:36 PM	#7
croweld89 New Member Join Date: Mar 2002 Posts: 34	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.

Apr 12th, 2002, 01:00 PM	#8
cpc New Member Join Date: Mar 2002 Posts: 38	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?

Apr 12th, 2002, 01:17 PM	#9
croweld89 New Member Join Date: Mar 2002 Posts: 34	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

Apr 12th, 2002, 02:30 PM	#10
Scott R Board Regular Join Date: Feb 2002 Location: WA state Posts: 332	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.