Is there a way to dynamically continue numbering between tables?

[rangial]

Hello
I am trying to have a consistent numbering between tables since I am trying to have a consistent numbering of a general Operation ID that is present on all the tables (that number is like a transaction ID but its present on different tables because there are different types of transactions but it is important to know the overall ID of that transaction, regardless the number sequence in the given table)

I already research different forms of numbering like this one


https://trumpexcel.com/number-rows-in-excel/


But all of them are using only one table, not a sequential dynamic one like what I am trying to do.

So. What I am trying to do is going from this (where there is numbering sequence for a unique table)

To this (where there is numbering sequence between tables; I added color references to represent the "jumps" of numbering between columns)

I am trying to automate this process instead doing it manually. So ideally it would like store the current Operation ID (maybe a VBS variable or simply a formula) and whenever I enter a new data on a given table it would continue the previous Operation ID
Any suggestions or ideas are welcome
Thanks in advance!

 

[John_w]

Welcome to MrExcel forums, but please post links to your cross-post(s). See forum rule 13 - https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

See if this Worksheet_Change event handler works for you. Put the code in the sheet module of the sheet containing the 3 tables - view the module by right-clicking on the sheet tab and clicking View Code.

The code reads and updates a named range called NextOperationID which you must define as a single cell outside the tables. This cell contains the next Operation ID to be assigned to a new table row. For your 2nd picture example this cell would contain 46.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim table As ListObject
    
    If Target.Count = 1 Then
        'Loop through all tables on this sheet to determine which table the worksheet change occurred in
        For Each table In Me.ListObjects
            'Does this table have at least 1 data row (and it isn't empty)?
            If Not table.DataBodyRange Is Nothing Then
                'Did the worksheet change occur in a data row?
                If Not Intersect(Target, table.DataBodyRange) Is Nothing Then
                    'Is the first column in this table (the Operation ID column) on the same row as the changed cell empty?
                    If IsEmpty(table.DataBodyRange(Target.Row - table.DataBodyRange.Row + 1, 1).Value) Then
                        'Put the next Operation ID in the first column and increment it
                        table.DataBodyRange(Target.Row - table.DataBodyRange.Row + 1, 1).Value = Range("NextOperationID").Value
                        Range("NextOperationID").Value = Range("NextOperationID").Value + 1
                    End If
                End If
            End If
        Next
    End If
    
End Sub