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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
689
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,929
Messages
5,703,225
Members
421,285
Latest member
Bebek

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
Top