Remember Me?

#1
June 23rd 05, 11:08 PM
 mike Posts: n/a
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
June 24th 05, 12:25 AM
 Max Posts: n/a

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
June 24th 05, 12:35 AM
 bigwheel Posts: n/a

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,)

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post littlegreenmen1 Excel Discussion (Misc queries) 0 June 10th 05 09:40 PM David Excel Worksheet Functions 4 May 25th 05 10:39 PM Metalteck Excel Discussion (Misc queries) 2 May 24th 05 08:18 PM pfwebadmin Excel Discussion (Misc queries) 0 May 17th 05 02:31 PM Beth Excel Worksheet Functions 3 December 1st 04 07:01 PM

All times are GMT +1. The time now is 03:24 AM.

The comments are property of their posters.