Excel, solution to the dynamic radnumreringar…I often use in my work

Here's a supertipps of dynamic line numbering.
How often have you not a column with row numbers and then to remove a line or add a line and then the line numbering destroyed and one must “donation of” entire table.

Here's a damn good tips to fix this:

So when you want to display a long list that you delete or add rows on , using the formula:
=ROW()-n (where n is the number of lines before the first row of the list,)
So, t.ex, if you have a list of names in column B, starting in cell B5 and you want line numbers in column A, it should be following =ROW()-4 In cell A5, and then copy the formula down the entire column A to the bottom of your list. So every fomel in column A will be =ROW()-4. ROW()-Part of the formula retunerar row number of the cell in which the formula . Since the number of lines before the first line is subtracted.

For example,: formula in cell A5 is = ROW()-4. ROW() returns 5 (IU. wheel 5) and subtraherar 4 gives 1 for your sequence numbers.
Once you delete a row in your data row numbers automatically adjusted throughout the table. When you add a row, all the row numbers automatically adjust when you have copied the formula from the cell above the new row.

There is a “plump” in this solution, however,.
If rows are deleted or that it adds to a series of (wheel 1 to 3 of this example) you will get the wrong values ​​in the row numbers. Here is the solution.

1) Choose a celll directly above the first row of your data (A4 in this example).

2) Give a name to the cell by typing a name in the Name box (IU. top) (the white bar immediately above cell A1)

3) No, use the same formula as above, replace 4 mot R0W(top).

Your new formula will now be: =ROW()-ROW(top)

 

Leave a Reply

Your email address will not be published. Required fields are marked *