Table autofill with serial number

crtnAF

New Member
Joined
Jun 13, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Hello. I have been slightly frustrated by searching solution for my little specific issue.

I have a excel table where I register my invoices. As for usual autofill, when we start putting in any value right below the table - the table expands itself and inserts the formulas from the above rows.
What I need is an automatic unique row serial number/ID. My table is from several columns and the data might be filtered. Using the casual =ROW(A1) doesn't do the job, because the given number/id will not stick with the row when you sort the table differently.
For now I just put in a value by hand, but the problem is that the last entry could be filtered away, so I have to remember always to clear my filters and sort them in ascending order at serial number/ID column before I enter new row.

So the question is,
For the given example of table:
1623610884865.png

By not being afraid that the order is sorted differently and some rows might be hidden away (in example ID 4), I just start a new entry by entering next date below and it will autofill the table column [number/ID] with next unique number (should be 6 in this one)? And if I sort them by [summ] ascending - their IDs stick to the row?

Any help is much appreciated!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi. Maybe a macro could do the job.
Paste a copy of the code below to the sheet module (right click on sheet tab | View Code).
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Count > 1 Then Exit Sub
 If Target.Column <> 2 Or Target.Value = "" Then Exit Sub
 If Target.Offset(, -1).Value <> "" Then Exit Sub
 Target.Offset(, -1).Value = Application.Max(Columns(1)) + 1
End Sub
How it should work - after you manually insert content in any cell of column B and if the corresponding cell in A is empty, then the code will insert the next ID number in A.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top