+ Reply to Thread
Results 1 to 3 of 3

Two Questions

  1. #1
    mike
    Guest

    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. #2
    Max
    Guest

    Re: Two Questions

    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 <at>yahoo<dot>com
    ----
    "mike" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    bigwheel
    Guest

    RE: Two Questions

    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 Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1