Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default inserting rows without adjusting formula relationships

I currently have a spread sheet that contains many rows of measurements. The
measurements are essentially all the same, taken over and over again for an
average.

I have the spreadsheet set up to calculate the required numbers
automatically, using some "IF" statements to accuont for different
situations. if the user only uses the spreadsheet as is it works well, but
one situation is when a measurement is taken twice, but counted as the same
(Averaged). To do this the user should be able to add a row and drag the
formulas down where needed.

When I do this, though, the referencing gets messed up. for example, if
there is a reference from cell A1 to cell A2, when I add a row between them
cell A1 changes the reference to A3, instead of leaving it as A2.

I could add $'s to preserve the cell numbers, but then that causes problems
when a user tries to add a new row for a brand new measurement. (maybe they
took more than normal and need more rows). same issue when deleting rows if
there are more rows than measurements taken.

So how do I have cell A1 not compensate for the new or deleted rows?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default inserting rows without adjusting formula relationships

If you want the formula to always reference A2 no matter what, just
substitute INDIRECT("A2") to everywhere that A2 appears in your formula

-Simon


"Daiv" wrote:

I currently have a spread sheet that contains many rows of measurements. The
measurements are essentially all the same, taken over and over again for an
average.

I have the spreadsheet set up to calculate the required numbers
automatically, using some "IF" statements to accuont for different
situations. if the user only uses the spreadsheet as is it works well, but
one situation is when a measurement is taken twice, but counted as the same
(Averaged). To do this the user should be able to add a row and drag the
formulas down where needed.

When I do this, though, the referencing gets messed up. for example, if
there is a reference from cell A1 to cell A2, when I add a row between them
cell A1 changes the reference to A3, instead of leaving it as A2.

I could add $'s to preserve the cell numbers, but then that causes problems
when a user tries to add a new row for a brand new measurement. (maybe they
took more than normal and need more rows). same issue when deleting rows if
there are more rows than measurements taken.

So how do I have cell A1 not compensate for the new or deleted rows?

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
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
Getting all rows of data that have a value in a particular column Rikki-Handgards Excel Discussion (Misc queries) 30 December 10th 07 01:29 PM
repeat a formula same column same number of rows apart steveo Excel Discussion (Misc queries) 2 July 9th 06 04:34 AM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Automatically inserting rows ausdiver99 Excel Worksheet Functions 1 June 2nd 05 02:15 PM


All times are GMT +1. The time now is 07:27 PM.

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

About Us

"It's about Microsoft Excel"