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 |