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

R

Rere

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!!!
 
S

surg4u1975

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

Ron Coderre

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
 
R

Rere

I will try to do it this way. I am so a novice at this. I inherited this.
I'll let you know.
 
R

Rere

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

Ron Coderre

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
 
R

Rere

Well, I didn't personally put it there, but yes, that's where it is. I've
just been handed this to update today.
 
R

Ron Coderre

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
 
R

Ron Coderre

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top