Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 23rd 05, 11:08 PM
mike
 
Posts: n/a
Default 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   Report Post  
Old June 24th 05, 12:25 AM
Max
 
Posts: n/a
Default

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

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
A couple of questions... littlegreenmen1 Excel Discussion (Misc queries) 0 June 10th 05 09:40 PM
Where can I post Macro questions? David Excel Worksheet Functions 4 May 25th 05 10:39 PM
Questions??? Metalteck Excel Discussion (Misc queries) 2 May 24th 05 08:18 PM
Pivot Table for survey data w/ questions as Rows & poss answrs as pfwebadmin Excel Discussion (Misc queries) 0 May 17th 05 02:31 PM
2 Questions Beth Excel Worksheet Functions 3 December 1st 04 07:01 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017