how do you delete cells when they are connected with a formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All I know how to do is enter info into a cell. This is a list of numbered
employees. Usually, I would just seleted the cell and delete, but I get
###error. The formula is =+sum (A73+1) Please help!!!
 
I believe you are seeing ### because the cell is not large enough to
display the #REF! error. When you delete the cell (A73), either by
right clicking and seleting delete or by using the Edit function, the
formula no longer knows what cell to reference, and all the other cells
below the one with the error have all adjusted accordingly (in this
case, up one). If you look below the cell with the error, you will see
that it now has =+sum(A73+1).

You may either delete the cell with the error also, or you will need to
select the cell and change the portion of the formula with the #REF! to
A73 again. If you choose that option you will need to fill the
remaining cells with the updated formula. Another option is to delete
the entire row to maintain the function's integrity.

If this does not help, please reply with a bit more information.
 
I think I understand what you're facing....

It seems like you want to use formulas to create a numerical
sequence(1,2,3...), and you do that by adding 1 to the cell above the formula
cell, right? The problem occurs when you delete the referenced cell's row.
Example:
A10: =+SUM(A9)+1
-->iF row 9 is deleted...the formula returns an error.

Here are some alternatives to that approach:

A10: =OFFSET(A10,-1,0)+1
(ALWAYS adds 1 to the value of the cell directly above the formula cell)
or
A10: =MAX($A$1:OFFSET(A10,-1,0))+1
(Adds 1 to the previous max value above the formula cell...allows for blank
cells in the referenced range)

Am I on the right track here?
Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
I will try to do it this way. I am so a novice at this. I inherited this.
I'll let you know.
 
Ok Ron, it's now telling me I have too few arguments.????This is the way I
did it: I'm deleting the row where Charlie's name is, so its: A73+1
=MAX($A$1:OFFSET(A73,-1,0))+1

Now, after I put in this info do I hit enter or what?
 
This formula:
=MAX($A$1:OFFSET(A73,-1,0))+1

should be in Cell A73. Is that where you put it?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Well, I didn't personally put it there, but yes, that's where it is. I've
just been handed this to update today.
 
Let's back up a step or two.....

Put this formula in Cell A2, then copy it down as far as you need it.
=MAX($A$1:OFFSET(A2,-1,0))+1

Now you should be able to delete any row (except row 1) without creating
formula errors.

Does that fix the problem?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
You are probably getting ###### in the cells because the numbers cannot fit
in the cells.

Try this:
Select all of the number cells
Format>Cells>Number tab
Category: Number
Decimal Places: 0
Click [OK]

If you still see ######, make the column width wider by doing this:
Click on the column heading
Format>Column>Width>Autofit Selection

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Back
Top