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!!!
0
Rere (7)
12/28/2005 3:57:03 PM
excel.newusers 15348 articles. 2 followers. Follow

10 Replies
459 Views

Similar Articles

[PageSpeed] 48

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?action=getinfo&userid=28718
View this thread: http://www.excelforum.com/showthread.php?threadid=496414

0
12/28/2005 4:36:59 PM
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!!!
0
12/28/2005 5:03:02 PM
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!!!
0
Rere (7)
12/28/2005 7:38:06 PM
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!!!
0
Rere (7)
12/28/2005 8:06:03 PM
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!!!
0
12/28/2005 8:16:02 PM
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!!!
0
Rere (7)
12/28/2005 8:35:02 PM
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!!!
0
Rere (7)
12/28/2005 8:45:04 PM
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!!!
0
12/28/2005 8:57:03 PM
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!!!
0
Rere (7)
12/28/2005 9:15:03 PM
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


"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!!!
0
12/28/2005 9:28:03 PM
Reply:

Similar Artilces:

Notes or comments in formula bar
Hello, I vaguely recall an article I read that mentioned I could insert a note or comment into the formula bar. Can someone tell me how to do that? I'm not talking about Insert-> Comment. Example: If I were to have A1+B1, there's a trick that will allow me to add a note within the formula bar such as "this formula adds apples and oranges." Thanks =A1+B1+N("Type Note Here") >-----Original Message----- >Hello, > >I vaguely recall an article I read that mentioned I could >insert a note or comment into the formula bar. Can >someone...

I've got a formula for stones and pounds
Hello, I've got a column that displays a series of weights in stones & pounds (both in the one cell). How do I get a chart to recognise and display in the same format? Regards, mikelenno What is the format of the cells with stone & pounds It is best to enter such values a 10 3/14 and format the cells with # ??/14 best wishes from one who is INT(275,14) stone & MOD(250,14) lbs -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "mikelenno" <mikelenno@discussions.microsoft.com> wrote in message news:5AD608FA-5367-4CED-AD76-1F54228EFDBA...

Deleting a half sent message
Sorry, forgot to say that I have Outlook 2003:) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

Deleted exchange 5.5 site not replicating over the ADC to Exchange 2000 side
We are a part of a shared Exchange 5.5 organization but our site is primarily Exchange 2000/2k3. We maintain a single 5.5 server as the rest of the Org is 5.5. The problem we are seeing is when the folks who control the Organization at the top level remove a site that is no longer used, that deletion is not carrying over the CONFIG_CA agreement to the Exchange 2000/AD side. Our Exchange 5.5 server executes the deletion with no issue. Any ideas on why this deletion is not replicating properly over the ADC and throught the SRS database? In the past we have manually removed the sites/recipie...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Connecting to BofA
anyone setup their sitekey and having problems with "update"? i created the sitekey like on tuesday and now when i do the update it fails constantly :(:(:(:( Try calling BofA's online banking dept at 800.933.6262 "w0rm3y" wrote: > anyone setup their sitekey and having problems with "update"? i created the > sitekey like on tuesday and now when i do the update it fails constantly > :(:(:(:( actually going to call again, i called wednesday (day before thanksgiving) and believe it or not they were already closed and i've sent them message...

IF / SUM
I have a spreadsheet with 17 rows, each representing a project name. In the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

Formula for ageing debt
I'm trying to do a spreadsheet so that when I put a debt amount in the first column, after 10 days it will automatically move to the next column and move on every 10 days till a 60 days column where it gets added to any amount already there. Can anyone clue me up to a formula to do this? Thanks -- Barnie Gumble Esq You will have to devise a way to associate the date of initial entr with the entry itself, then it is relatively simple to do - could yo have a lookup table on a separate sheet that returned entry dates fo all amounts, BUT the amounts would have to be unique, ie n duplica...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

Deleted file in Outlook
I accidentaly deleted a folder in Outlook 2003 and am wondering if it possible to retreive it. Thank you, Karl youthminman@yahoo.com <youthminman@yahoo.com> wrote: > I accidentaly deleted a folder in Outlook 2003 and am wondering if it > possible to retreive it. Unless you permanently deleted it (i.e., held down Shift when pressing Delete), it will be in your Deleted items folder, unless you've emptied that folder. If you have emptied it, other suggestions depend on knowing what data store you're using (PST or Exchange mailbox). -- Brian Tillman ...

Why won't formulas obey vertical alignment commands?
I have a spreadsheet with numbers and text. Many numbers are derived from cell formulas. I can vertically align the text in all the cells in the numbers/text worksheet, but if I change the numbers to display formulas only, then the cell values automatically align at the bottom of the cell no matter what I have set the alignment to be. I just want to align the formulas at the tops of the cells so that I have room to write underneath them on a printed copy. Is this possible? Why not increase the Row height and write *above* them? -- HTH, RD =============================================...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

How to select other random cells
I have a 2 part question. I want to have a 1 question survey randomly filled out by 200 people. Column A lists the 1 questions. Columun b has ABCDE listed in cells B6 to b10. I would like 200 samples in columns c, d, e, etc. How do I set this up so that they randomly Coose B6 to B 10. Second part. On another spreadsheet, I have a similar situation, but the user has 10 answers to select from and I want them to randomly select all that apply. How would I do this? Thanks! ...

Can't delet Inbox messages.
I can not delete anything in my inbox. I can send email and view my email but when I highlite the message I can not delete...not even with the keyboard delete key. Any ideas would be appreciated. Thanks. More info, please - version and SP level of Outlook? Size of PST file? Ken wrote: > I can not delete anything in my inbox. I can send email > and view my email but when I highlite the message I can > not delete...not even with the keyboard delete key. Any > ideas would be appreciated. Thanks. ...

Formatting in the formula bar
When i type in a number into a cell in my worksheet, say: 42.99 Excel automatically rounds it to 43. Which is what i want and what i set it up to do. However, the number in the formula bar also rounds to 43. Normally i thought the formula bar stayed at 42.99 and only the spreadsheet cell rounds to 43. I am using Excel 2000. Please help asap as i need the formula bar to stay at 42.99 so i remember what the original number was!!! Hi maybe you have checked 'Tools - Options - Calculation - Precision as displayed' -- Regards Frank Kabel Frankfurt, Germany Beccy wrote: > When ...

outlook can not connect to the internet
I just installed outlook 2003 and when I try to load it it directs me to set up an e-mail account. When I do this thw e-mail manager says it can not connect to the internet. I can browse the web fine. Any ideas? stevechild <stevechild@discussions.microsoft.com> wrote: > I just installed outlook 2003 and when I try to load it it directs me > to set up an e-mail account. When I do this thw e-mail manager says > it can not connect to the internet. I can browse the web fine. Any > ideas? Please list the exact steps you take to create the account. -- Brian Tillman ...

delete outlook.pst
Can I delete this file and have Outlook open up as if I had never used it before? TIA Rather than delete it, why not just rename it to outlook.bak? Then when you open Outlook, you will have a chance to create a new .pst file. If you want to delete it because of problems with it, why not tell us what the problem is so someone may be able to help you? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, RA asked: | Can I delete...

Unable to connect Office 2003 to SBS Exchange 2000
I have two new XPSP2 workstations with Office 2003 but when after configuring them for the Exchange server I get one of three errors: a) on a profile migrated from an existing workstation (whose Office 2000 still works) Cannot start Microsoft Office Outlook. Unable to open the Outlook window. The set of folders could not be opened. b) on a new profile using Cached Exchange Mode Unable to open your default e-mail folders. You must connect to your Microsoft Exchange Server computer with the current profile before you can synchronize your folders with your offline folder file. c) on a new...

Formula Problem #11
I have an excel sheet that has almost 4000 data rows. I need to compare the old sheet to the new sheet and if the part number is equal, I need it to show me the discount from the old sheet in a column in the new sheet. Here is the formula I came up with: =LOOKUP(A4,old!A4:A4000,old!H4:H4000) This compares the A column in the new sheet with the A column in the old sheet and then will report the discount from the H column into the column the formula is written. If I hand type the formula in ever cell changing the row number for the look up cell it works fine. However, when I try to dr...

How do I call individual cell data from an Excel sheet into Powerpoint or Word?
Ok, I have an Excel work sheet done up to calculate discounts given to employees based on their employer. I now need to make a flyer in either Word or PowerPoint (or another program if needed) but I'd like for it to pull the data from individual cells in the Excel sheet. For instance in the midst of the graphics and flyer text that will remain the same for every flyer I'd like to be able to tell it to refer to Excel document, and then pull all the information for say, the company in row 7 (since 7 is the first company listed) It would need to pull the text (co. name) from A7, sug. r...