dynamically changing the numbers in the cell based

Question

I Have a excel sheet under column a I have say 10 values cell A1 = 1, cell A2 = 2, cell A3 = 3.....cell A10 = 10 Now suppose a user changes the value in cell A3. say cell A3 = 1 so now all the other values should change dynamically say A1 will change to 2, A2 will change 3, A3 is already 1, A4 will change to 4 and so on... similarly if any other values changes the rest values should be arranged dynamically..keeping only the unique 10 values. no values should be repeated.

I have tried achieving this by copying the values but unable to do so. enter image description here

Answer 1

You can just do a formula saying adding 1 from the next one up

A1=1
A2="=A1+1"
A3="=A2+1"
A4="=A3+1"
A5="=A4+1"
A6="=A5+1"

if you copy and paste it should automatically change

am I not understanding your issue properly?

Answer 2

Try this Change event for the worksheet ...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myVal As Variant
    Dim iCount As Long

    Dim cell As Range
    Dim myRange As Range

    Set myRange = Worksheets("Sheet1").Range("A1:A10")

    If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False

    myVal = Target.Value
    iCount = 1
    For Each cell In myRange
        If Intersect(Target, cell) Is Nothing Then
            If iCount = myVal Then
                iCount = iCount + 1
            End If
            cell.Value = iCount
            iCount = iCount + 1
        End If
    Next cell


    Application.EnableEvents = True

End Sub

... since you asked for a revision. This uses "a_" in front of the number to show that it's auto generated and "u_" to show it's user generated.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myVal As Variant
    Dim iCount As Long
    Dim usedString As String
    Dim BypassChange As Boolean

    Dim cell As Range
    Dim myRange As Range

' if the changed range is not of interest, bail out
    If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

' get setup for the work
    Set myRange = Worksheets("Sheet1").Range("A1:A10")

' usedString will be used to determine if a number has already be set by the user
    usedString = " "
    For Each cell In myRange
        If Left(cell.Value, 2) = "u_" Then
            usedString = usedString & "-" & Right(cell.Value, Len(cell.Value) - 2) & "-"
        End If
    Next cell

' check to make sure the user hasn't specified the same number twice. If he has
' BypassChange will make sure the change gets removed later.
    myVal = Target.Value
    If InStr(usedString, "-" & myVal & "-") > 0 Then
        MsgBox "Value already specified -> " & myVal
        BypassChange = True
    End If

' here's the work
    Application.EnableEvents = False

    iCount = 1
    For Each cell In myRange
        If Intersect(Target, cell) Is Nothing Or BypassChange Then
            If Left(cell.Value, 2) <> "u_" Then
                If iCount = myVal Then
                    iCount = iCount + 1
                End If
                Do While InStr(usedString, "-" & iCount & "-") > 0
                    iCount = iCount + 1
                Loop
                cell.Value = "a_" & iCount
                iCount = iCount + 1
            End If
        Else
            cell.Value = "u_" & myVal
            usedString = usedString & "-" & Right(cell.Value, Len(cell.Value) - 2) & "-"
        End If
    Next cell

    Application.EnableEvents = True

End Sub