| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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!!! |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
how do you delete cells when they are connected with a formula
You are probably getting ###### in the cells because the numbers cannot fit
in the cells. Try this: Select all of the number cells FormatCellsNumber 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 FormatColumnWidthAutofit Selection Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Rere" wrote: 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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible? | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| Delete duplicate Cells (formula) | Excel Discussion (Misc queries) | |||
| quick way to copy-paste a formula linked to cells in another file | Excel Worksheet Functions | |||
| Locate and delete specific cells | Excel Discussion (Misc queries) | |||
Linear Mode
