Updating relative addresses 1

[GBall]

I've got a column of formulae, each of which references the one above it. If I delete a row, I get #REF! in all the rows below the one just deleted and I have to go through and refill them.

This is the formula. It builds a series of numbers in the format 1.7.10... upwards. This is in B12 (I1 is the increment)
=LEFT(B11,(LEN(B11)-(LEN(B11) - FIND(".",B11,3))))&RIGHT(B11,LEN(B11) - FIND(".",B11,3))+$I$1

Now, if I delete the row B11, I would expect this and subsequent rows to be amended downards.
Instead, I get #REF.
I am working with Excel 95.

TIA. Regards,
Graham

 

[DaleWatson123321]

Graham,

I've created a file based on your description, and created a "macro" solution - which works.

Because you have a situation where you are deleting a formula upon which other formulas rely upon, there is really no way to avoid getting the #REF!

I believe your best option is to use the following (relaitvely simple) VBA code. Activate it after a row or rows are deleted. I've attached the code to a "macro button" which when clicked, restores your formulas "in the blink of an eye".

Sub copy_formula()
'restores formulas - for use when row(s) deleted

Application.ScreenUpdating = False
'turns off screen updating

Application.Goto Application.Range("startcell&quot
'goes to the cell above the first formula

ActiveCell.Offset(1, 0).Select
'goes down 1 row - to 1st row with formula

Application.Range("frm&quot.Copy
'copies stored formula to memory

Range(ActiveCell.Address, ActiveCell.End(xlDown)).Select
'selects range of cells until hitting a "blank" cell

ActiveSheet.Paste
'pastes the formula from memory

Application.Goto Application.Range("A1&quot
'goes to the A1 position

Application.ScreenUpdating = False
'turns screen updating back on

End Sub

NOTE: For this code to work, it required that you first do the following:

1) Create a "Range Name" for the "starting number". I have placed this "starting number" in the cell immediately above your first formula in cell B12. Therefore, assign the Range Name "startcell" to cell B11. The "easy" method of creating a Range Name is to go to that cell (B11), use Control-F3 (hold down the Control key and hit the F3 key), type the name "startcell", and hit the Enter key.

2) Assign a Range Name to the cell where you will want to "store" a copy of your formula. I have used the cell under your "counter" cell (I1). Therefore, in I2, assign a Range Name called "frm" (short for "formula&quot.

3) Then copy your formula from B12 to I2. This then becomes your "stored" copy of your formula - which is in turn used by the VBA code to replace any and all formulas which are affected by deleting rows.

That's it, that's all. "Works like a charm".

If you would like a copy of the actual file, for easier reference, just email me, and I'll send the file via return email.

Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GBall]

Wow ! Thanks Dale.
Just one problem.
After it has finished, the "frm" cell still has the 'copy' line running round it and the status bar is asking me to press enter or choose paste.
Hitting escape does the trick, but I haven't found a reset method yet. Regards,
Graham

 

[DaleWatson123321]

Graham,

I'm pleased to hear that you're happy with the solution, and THANKS for the STAR.

To clear the "copy line", add the following line to the end of the code:

Application.CutCopyMode = False

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GBall]

Excellent !!
Thanks again.
Regards,
Graham