Two Questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike - 23 Jun 2005 23:08 GMT

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.

Reply to this Message

Max - 24 Jun 2005 00:25 GMT

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.

Signature

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> I am using W98 and Excel 2K 9.0
>
[quoted text clipped - 16 lines]
>
> Thanks for any and all help.

Reply to this Message

bigwheel - 24 Jun 2005 00:35 GMT

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 to this Message