Automatically update formula

Automatically update formula

Didn't find your answer?

I currently have an Excel spreadsheet where I have a row number in column "A" running down the spreadsheet.
Is there a way that I can get excel to automatically update this number, (and also subsequent row numbers) after a row has been inserted?Any help gratefully received.
Thanks,
Paul Caddick

Replies (10)

Please login or register to join the discussion.

avatar
By User deleted
14th Mar 2009 16:28

Automatically update formula
Dear Paul,

Try this:

Assuming that your sequential numbering begins at A6 and the first number is 1 then in A7 type this formula:

ROW()-5

Copy this formula to cover the extant of your data.

When you do insert a row, simply copy the formula from the cell above. All the other cells below would have automatically changed.

Note that the 5 above represents the unused cells above the first numbered cell.

Good luck,

Ashraf

Thanks (0)
avatar
By User deleted
13th Mar 2009 16:34

Renumbering after Inserting a row
I assume that you want the rows numbered so that you can do things like sorting and resorting back into the original order, in which case the numbers need to be values, not formulae.

Below is a quick and dirty solution which assumes that your table starts on Excel row 6

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/03/2009 by Spiney Norman
'

'
ActiveWorkbook.Names.Add Name:="Top", RefersTo:="=A6"
Range("Top").Select

' Move to the next row and note it for Autofill later
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveWorkbook.Names.Add Name:="Top1", RefersTo:=ActiveCell

' Now find the empty gap where the row was inserted
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

' We are now at the inserted row, put a 'z' in the empty gap
ActiveCell.FormulaR1C1 = "z"

' Now find the bottom of the range to be renumbered
Selection.End(xlDown).Select
ActiveWorkbook.Names.Add Name:="Bottom", RefersTo:=ActiveCell

' Do the renumbering
Range("Top:Top1").Select
Selection.AutoFill Destination:=Range("Top:Bottom")
End Sub

Thanks (0)
avatar
By b.potter
13th Mar 2009 14:59

Simple solution
On the trial you carried out before, instead of copying the formula down the whole spreadsheet, just edit the new row cell to the one above + 1, all the other cells will renumber themselves.

Thanks (0)
avatar
By chris.lee99
13th Mar 2009 13:12

Silly Question
This might seem like a silly questions, but what is the purpose of having the row numbers in the spreadsheet itself. Surely you have the row numbers to the side of the sheet, and if you want these to show when printing, then could you not just set the row and column heading in the page set-up.
If it relates to a record number, then would you not be better to use access as you can get the table to automatically assign a unique number whenever data is inserted, then yoou can odbc this back to your spreadsheet for analysis etc...

Just my 2pensc worth.

Thanks (0)
avatar
By David160
06th Mar 2009 16:56

Use =row() This returns the row number for the row the cell is in. If you insert a row, it updates. You could also do an offset by adding 1 to row (), to give =row()+1.

Thanks (0)
avatar
By ACDWebb
06th Mar 2009 14:20

Heavy handed VBA option
I think this works (seems to on a limited test)

In the VB editor for the Worksheet you want the numbers to appear in Col A paste the following code

'code starts
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tRows 'holds number of rows selected
Dim r ' loop counter
If Target.Column = 1 Then 'make sure the change is in Col A
With Target
tRows = Target.Rows.Count 'count the rows selected
For r = 0 To tRows 'loop through each selected cell in Col A
'If cell A? is blank
If Target.Cells(r, 1) = "" Then _
Target.Cells(r, 1).FormulaR1C1 = "=ROW()" 'add the formula returning the row number in cell A? Amended to use ROW instead of "=CELL(""row"",RC)"
Next r 'loop
End With
Calculate ' added to recalculate formulae.
End If 'if change not in Col A do nothing
End Sub

'code ends

This may have unexpected effect on other things you do when copy/ pasting for example.

EDIT - by the way this is only to automate adding the CELL formula to blank cells when you insert rows. As in later comments if the old formulae moved down by the insert do mot updat it probably means Calculation is set to Manual

Thanks (0)
avatar
By listerramjet
06th Mar 2009 16:00

autoupdate =CELL("row",A1)
If you insert rows between then the reference should change - so for example insert a row at a27 and what was a27 becomes a28, and the formula in that cell changes to =CELL("row",A28). All the formula does is give you the row number of the cell referenced in the formula.

Perhaps you have calculation set to manual? In which case inserting a row will have no effect until you press recalc.

Thanks (0)
avatar
By paul.malcolmpiper.co.uk
06th Mar 2009 13:35

=CELL("row"A1)
Just tried the above and inserted a row , but the row numbers below the inserted row dont automatically update. As the spreadsheet runs for a number of pages I wondered if there was any way of getting the cells below the inserted row to update automatically, rather than having to copy the formula all the way down the spreadsheet each time I insert a new row?
The numbers are just a sequential sequence that I want to update the inserted and subsequent rows.

Thanks (0)
avatar
By adam.arca
06th Mar 2009 13:30

It all depends!
Without going into detail, it does all depend on what you've got and what you're trying to achieve. I'm also assuming your numbers in col "A" are sequential.

If you want Excel to update automatically, then you'll have to use VBA. You'll need a procedure which pre-empts the "right click" event on your mouse (assuming you do right click on the grey border to insert new rows), inserts a row and also a value / formula into the new row. VBA could be used to update all superseding rows but it would probably be easier to have a formula in col "A" which refers to the previous row, eg: A7=A6+1, and then normal Excel functionality does the rest.

Avoiding VBA, can you achieve what you want simply by using a formula like the one I suggested and manually copying into the new row? If your numbers in col "A" are related to the row numbers in some way, then you could also use the Row() function to return a value.

Thanks (0)
avatar
By listerramjet
06th Mar 2009 13:23

how about
=CELL("row",A1) returns a 1. put it in a1 and then copy it down and you get row numbers as you describe. insert a row and they update. You have to copy it into the new row!

Thanks (0)