If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how do you delete cells when they are connected with a formula
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!!! |
#2
|
|||
|
|||
how do you delete cells when they are connected with a formula
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. -- surg4u1975 ------------------------------------------------------------------------ surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718 View this thread: http://www.excelforum.com/showthread...hreadid=496414 |
#3
|
|||
|
|||
how do you delete cells when they are connected with a formula
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 "Rere" wrote: 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!!! |
#4
|
|||
|
|||
how do you delete cells when they are connected with a formula
I will try to do it this way. I am so a novice at this. I inherited this.
I'll let you know. "Ron Coderre" wrote: 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 "Rere" wrote: 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!!! |
#5
|
|||
|
|||
how do you delete cells when they are connected with a formula
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? "Ron Coderre" wrote: 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 "Rere" wrote: 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!!! |
#6
|
|||
|
|||
how do you delete cells when they are connected with a formula
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 "Rere" wrote: 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? "Ron Coderre" wrote: 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 "Rere" wrote: 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!!! |
#7
|
|||
|
|||
how do you delete cells when they are connected with a formula
Well, I didn't personally put it there, but yes, that's where it is. I've
just been handed this to update today. "Ron Coderre" wrote: 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 "Rere" wrote: 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? "Ron Coderre" wrote: 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 "Rere" wrote: 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!!! |
#8
|
|||
|
|||
how do you delete cells when they are connected with a formula
Sorry, Ron. I just re-read your post. Yes, this is where I'm putting it.
"Rere" wrote: Well, I didn't personally put it there, but yes, that's where it is. I've just been handed this to update today. "Ron Coderre" wrote: 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 "Rere" wrote: 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? "Ron Coderre" wrote: 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 "Rere" wrote: 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!!! |
#9
|
|||
|
|||
how do you delete cells when they are connected with a formula
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 "Rere" wrote: Sorry, Ron. I just re-read your post. Yes, this is where I'm putting it. "Rere" wrote: Well, I didn't personally put it there, but yes, that's where it is. I've just been handed this to update today. "Ron Coderre" wrote: 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 "Rere" wrote: 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? "Ron Coderre" wrote: 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 "Rere" wrote: 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!!! |
#10
|
|||
|
|||
how do you delete cells when they are connected with a formula
I'm sorry Ron, I don't get it!!! When I do that, it just puts #####in the
columns. "Ron Coderre" wrote: 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 "Rere" wrote: Sorry, Ron. I just re-read your post. Yes, this is where I'm putting it. "Rere" wrote: Well, I didn't personally put it there, but yes, that's where it is. I've just been handed this to update today. "Ron Coderre" wrote: 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 "Rere" wrote: 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? "Ron Coderre" wrote: 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 "Rere" wrote: 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!!! |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
checking that cells have a value before the workbook will close | kcdonaldson | Worksheet Functions | 8 | December 5th, 2005 04:57 PM |
Delete duplicate Cells (formula) | Ran | General Discussion | 2 | November 23rd, 2005 11:25 PM |
Formula works in some cells, doesn't in other | Wowbagger | New Users | 13 | June 30th, 2005 03:21 PM |
Applying formula to only NON-EMPTY cells in range | Tasi | General Discussion | 5 | March 29th, 2005 10:48 PM |
Arrary formula for Average includes blank cells | jan | Worksheet Functions | 4 | May 14th, 2004 11:19 PM |