Increment number in Table (without reacting to sort)

[Kamolga]

Hi,

I have a risks log table where the first column is just 1,2,3,4. but this column looks now like 3,1,4,2 because we sorted on a ranking or a category.
View attachment 45781
When a user needs to add a risk to the log, he should just manually add the next number (here 20 is easy but some files have hundreds) if the table is not sorted on the column number.

I could set a formula counting table rows above +1 or taking the max value of range above +1 but that number would then change for each line (risk #3 could become risk #1) whenever we would sort the table differently.

Any idea?

Thanks

 

[Crystalyzer]

How about a macro that will increment Column A under these conditions

1. The user enters a value in Column B
2. The cell in Column A is blank
3. The cell directly above the cell in Column B where the user entered the data is NOT blank

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Offset(0, -1).Value = "" And Target.Offset(-1, 0) <> "" Then
        Application.EnableEvents = False
        Target.Offset(0, -1).Value = WorksheetFunction.CountA(Range("A:A"))
        Application.EnableEvents = True
    End If
End Sub

 

[Peter_SSs]

Sounds like you don't want the first column to sort when the table is sorted, nor do you want the first column to remain as it is when the table is sorted.

Could you use a prominent cell like B1 below to advise the use what number they should use next if creating a new row?

21 08 28.xlsm
	A	B
1	Next Num:	6
2	Num	Date
3	4	3/03/2021
4	5	31/05/2021
5	2	25/07/2021
6	3	14/08/2021
7	1	15/08/2021
8
9
10
Table Index Number

Cell Formulas
Range	Formula
B1	B1	=MAX(Table3[Num])+1