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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rere - 28 Dec 2005 15:57 GMT

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

Reply to this Message

surg4u1975 - 28 Dec 2005 16:36 GMT

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.

Signature

surg4u1975

Reply to this Message

Ron Coderre - 28 Dec 2005 17:03 GMT

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

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

Reply to this Message

Rere - 28 Dec 2005 19:38 GMT

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

> I think I understand what you're facing....
>
[quoted text clipped - 26 lines]
> > employees. Usually, I would just seleted the cell and delete, but I get
> > ###error. The formula is =+sum (A73+1) Please help!!!

Reply to this Message

Rere - 28 Dec 2005 20:06 GMT

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?

Reply to this Message

Ron Coderre - 28 Dec 2005 20:16 GMT

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

> 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
[quoted text clipped - 32 lines]
> > > employees. Usually, I would just seleted the cell and delete, but I get
> > > ###error. The formula is =+sum (A73+1) Please help!!!

Reply to this Message

Rere - 28 Dec 2005 20:35 GMT

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

> This formula:
> =MAX($A$1:OFFSET(A73,-1,0))+1
[quoted text clipped - 43 lines]
> > > > employees. Usually, I would just seleted the cell and delete, but I get
> > > > ###error. The formula is =+sum (A73+1) Please help!!!

Reply to this Message

Rere - 28 Dec 2005 20:45 GMT

Sorry, Ron. I just re-read your post. Yes, this is where I'm putting it.

> Well, I didn't personally put it there, but yes, that's where it is. I've
> just been handed this to update today.
[quoted text clipped - 46 lines]
> > > > > employees. Usually, I would just seleted the cell and delete, but I get
> > > > > ###error. The formula is =+sum (A73+1) Please help!!!

Reply to this Message

Ron Coderre - 28 Dec 2005 20:57 GMT

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

> Sorry, Ron. I just re-read your post. Yes, this is where I'm putting it.
>
[quoted text clipped - 48 lines]
> > > > > > employees. Usually, I would just seleted the cell and delete, but I get
> > > > > > ###error. The formula is =+sum (A73+1) Please help!!!

Reply to this Message

Rere - 28 Dec 2005 21:15 GMT

I'm sorry Ron, I don't get it!!! When I do that, it just puts #####in the
columns.

> Let's back up a step or two.....
>
[quoted text clipped - 64 lines]
> > > > > > > employees. Usually, I would just seleted the cell and delete, but I get
> > > > > > > ###error. The formula is =+sum (A73+1) Please help!!!

Reply to this Message

Ron Coderre - 28 Dec 2005 21:28 GMT

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

> I'm sorry Ron, I don't get it!!! When I do that, it just puts #####in the
> columns.
[quoted text clipped - 67 lines]
> > > > > > > > employees. Usually, I would just seleted the cell and delete, but I get
> > > > > > > > ###error. The formula is =+sum (A73+1) Please help!!!

Reply to this Message