Home 
Search 
Today's Posts 
#1




Two Questions
I am using W98 and Excel 2K 9.0
Question 1. On my sheet I print the column letters and row numbers. I have cells that contain comments such as 'Total of Rows 5 To 8' or 'Row 15 Less Row 23'. The numeric portion of these comments, 5, 8, 15 and 23, represent actual row numbers. If I insert or delete a row it now makes the comments wrong and they must be changed. Is there a way to structure these comments so the numeric portion is a variable that will change as rows are inserted or deleted? Question 2. I have a column, say column C, where rows 5 to 10 contain numeric data. Row 12 contains the formula =Sum(c5:c10). This works fine. Sometimes when I insert a new row, say 8 or 11, the formula in what is now row 13 will change to =Sum(c5:c11) and sometimes it will not. I am unable to determine those conditions what conditions are controlling this action. Can someone shed some light on this? Thanks for any and all help. 
#2




Q1. 'Total of Rows 5 To 8'
To make the numbers in the phrase dynamic to subsequent row insertions / deletions, you could try something like: ="Total of Rows "&ROWS($A$1:A5)&" To "&ROWS($A$1:A8) Q2. Instead of say, in C12: =SUM(C5:C10), try including a "buffer" blank row below the "sum" range, i.e. use instead in C12: =SUM(C5:C11), where row 11 is to be left blank.  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  "mike" wrote in message oups.com... I am using W98 and Excel 2K 9.0 Question 1. On my sheet I print the column letters and row numbers. I have cells that contain comments such as 'Total of Rows 5 To 8' or 'Row 15 Less Row 23'. The numeric portion of these comments, 5, 8, 15 and 23, represent actual row numbers. If I insert or delete a row it now makes the comments wrong and they must be changed. Is there a way to structure these comments so the numeric portion is a variable that will change as rows are inserted or deleted? Question 2. I have a column, say column C, where rows 5 to 10 contain numeric data. Row 12 contains the formula =Sum(c5:c10). This works fine. Sometimes when I insert a new row, say 8 or 11, the formula in what is now row 13 will change to =Sum(c5:c11) and sometimes it will not. I am unable to determine those conditions what conditions are controlling this action. Can someone shed some light on this? Thanks for any and all help. 
#3




Answer 1
One way I've thought of is to enter the formula =ROW() into convenient cells on rows 5 and 8, say A5 and A8. Then for your comment enter the formula =TEXT("Total of rows " & A5 & " to " & A8,) Answer 2 If you have a formula =SUM(C5:C11) and you insert a row within that range i.e. 6  11, then the formula will change. If you insert a new row 5 or 12 however, the formula will not change. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
A couple of questions...  Excel Discussion (Misc queries)  
Where can I post Macro questions?  Excel Worksheet Functions  
Questions???  Excel Discussion (Misc queries)  
Pivot Table for survey data w/ questions as Rows & poss answrs as  Excel Discussion (Misc queries)  
2 Questions  Excel Worksheet Functions 