insert rows w/o updating cell references
Posted by stacy on January 04, 2002 7:58 AM
This may not be a VB question...but any help is appreciated. I have 2 sheets...sheet 1 refers to sheet 2. All of the cell references in sheet one have $ signs to keep them static. But....when I paste my "raw data" to sheet 2, I have to insert a couple blank lines in order for it to match Sheet 1's references. When I insert the rows, sheet 1 changes the references. I do not do want it to change references. I want them to stay the same all the time. Thanks for the help!!
Posted by Russell Hauf on January 04, 2002 8:58 AM
In your formulas in sheet 1, use the INDIRECT function if you want your formula to always refer to the same row numbers (regardless of whether you insert rows or not).
So, say your function in sheet 1 is now:
=SUM(Sheet2!$A$2:$A$50),
Try this instead:
=SUM(INDIRECT("Sheet2!$A$2:$A$50"))
Hope this helps,
Russell
p.s. I got this idea off of microsoftexceltraining.com - Dave Hawley's site - some good stuff there.
Posted by stacy on January 04, 2002 9:23 AM
Hi Russell, That doesn't seem to work for me. I am not summing. I am referring one cell to one cell. I took away the SUM from your solution, and it didn't work properly. I looked INDIRECT up in help, and it states that it is a way to link (Sheet 1) to a cell (in Sheet 2) and then have that cell referenced to another cell....which is the ultimate cell that displays in Sheet1. Please correct me if I am wrong or just royaly confused.....Thanks for the help...
Stacy
Posted by Russell Hauf on January 04, 2002 9:29 AM
Please paste your formula. Thank you. Hi Russell, That doesn't seem to work for me. I am not summing. I am referring one cell to one cell. I took away the SUM from your solution, and it didn't work properly. I looked INDIRECT up in help, and it states that it is a way to link (Sheet 1) to a cell (in Sheet 2) and then have that cell referenced to another cell....which is the ultimate cell that displays in Sheet1. Please correct me if I am wrong or just royaly confused.....Thanks for the help... Stacy
Posted by stacy on January 04, 2002 9:58 AM
The "regular" way that I do it is in a Sheet called 'Jan' I have the following: 'Paste Chart'!$I$14
If I go to the sheet called 'Paste Chart' and insert a row above I14, my cell reference in 'Jan' gets changed to $I$15.
Thanks for your time!! Please paste your formula. Thank you. : Hi Russell, That doesn't seem to work for me. I am not summing. I am referring one cell to one cell. I took away the SUM from your solution, and it didn't work properly. I looked INDIRECT up in help, and it states that it is a way to link (Sheet 1) to a cell (in Sheet 2) and then have that cell referenced to another cell....which is the ultimate cell that displays in Sheet1. Please correct me if I am wrong or just royaly confused.....Thanks for the help... : Stacy
Posted by Russell Hauf on January 04, 2002 10:48 AM
This should work (it does for me):
=INDIRECT("'Paste Chart'!I14")
There isn't even a need for the absolute references in this case! (The dollar signs).
Hope this helps,
Russell
