Getting sheetname and using for auto incremental ID

Copper Contributor

Hello Team,

 

I'm trying to create a form where i need to add the work packet and work item Id. Work packet should be the sheetname and work item should be sheetname +incremental logic.

For eg.

Sheet1 name - CH-2306011  

sheet2 name - CH-2306012

sheet3 name - CH-2307011

So, the work packet will be:
2306011
2306012,etc..
getting result with formula =SUBSTITUTE(TEXTAFTER(CELL("filename",S86),"]"),"CH-","") 

However, to get the work item logic is:
Remove "CH-" and find the initial 6digit number of the sheet and match it will all other sheets. If same number found assign 1 and +1 on next every same number

Eg:
2306011 - Work item=01 
2306012 - Work item =02
2307011 -  Work item = 01

Since above 2 WO was raised in June month, the WI should be 1 & 2,, +1 on same month found.

I've tried to used following formulas but finding some problems:
Added name =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Got the sheets name =IFERROR(INDEX(sheetnames,T92),"")
For incremental = =IF(ISNUMBER(MATCH(S88,Table7181920[[#All],[Column3]])),COUNTIF(Table7181920[[#All],[Column3]],S88),1)

Issues facing:
If new sheet added both previous and new sheet are having same WI as formula is updating in previous sheet to.

Table not getting refresh when trying to  change name of sheet after copy.

Need help!

Thanks in advance!

2 Replies

@Dineshja48 

To achieve the desired functionality of automatically generating incremental work item IDs based on the sheet names in Excel, you can use a combination of formulas and VBA macros.

Here is a step-by-step guide:

  1. Open the Visual Basic for Applications (VBA) editor by pressing ALT + F11 in Excel.
  2. Insert a new module by clicking on "Insert" > "Module."
  3. In the module, paste the following VBA code:
Function GetWorkItemID(sheetName As String) As String
    Dim ws As Worksheet
    Dim counter As Integer
    
    counter = 1
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> sheetName Then
            If Left(ws.Name, 2) = Left(sheetName, 2) Then
                If IsNumeric(Mid(ws.Name, 4, 6)) Then
                    If Mid(ws.Name, 4, 6) = Mid(sheetName, 4, 6) Then
                        counter = counter + 1
                    End If
                End If
            End If
        End If
    Next ws
    
    GetWorkItemID = Format(counter, "00")
End Function

 4. Close the VBA editor.

 

Now, you can use the custom function GetWorkItemID in your worksheet to automatically generate work item IDs based on the sheet names.

Here is how you can implement it:

  1. In the cell where you want to display the work item ID, enter the following formula:

=GetWorkItemID(SUBSTITUTE(TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"000000"),"CH-",""))

Note: Replace "A1" in the formula with the cell reference that contains the sheet name (e.g., the cell that contains CH-2306011).

This formula uses the SUBSTITUTE function to remove "CH-" from the sheet name, converts the remaining numeric part to text using the TEXT function, and then passes it to the GetWorkItemID function to retrieve the incremental work item ID.

Whenever a new sheet is added or the sheet names change, the formula will automatically update the work item IDs accordingly.

Make sure to save the workbook as a macro-enabled (.xlsm) file to preserve the VBA code.

Note: If you rename a sheet after copying, the table that holds the work item IDs may not update automatically. In this case, you can add a button or use a worksheet change event to trigger a macro that refreshes the table. Let me know if you need assistance with implementing that as well.

The steps were processed with the help of AI. 

All information always without guarantee.

It is always recommended to make a backup.

@NikolinoDE Hello,

 

Thanks for answering. I tried to implement your solution. However, the  auto increment is not happening. Also, i believe the previous sheet is getting updated with the count when new sheet is created.

 

Attached is the sample workbook i created with your code.

 

Let me know something wrong I did!