Forum Discussion

subash758's avatar
subash758
Copper Contributor
Oct 15, 2020
Solved

help with a formula - order data was entered

Hello

I am not able to find any tutorial online that shows a formula to do the below.

As I enter a value in the B column , I would like the ordered it was entered in to show up in the C column.

 

So lets say I enter a value in B2 the number 1 would appear in C2.

and then I entered a value in B6 the number 2 would appear in C4.

and then I entered a value in B5 the number 3 would appear in C5.

 

Would this be even possible?

 

excel
Formulas and Functions
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Oct 15, 2020

    subash758 

    I have used VBA to achieve the desired output.

    Right click on Sheet1 Tab --> View Code to see the code.

    The code is for Sheet Change Event so that once you input a qty in column B and if the corresponding cell in column A is not empty it would place the desired order sequence# in corresponding cell in column C.

    Code:

    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
If Target.Column = 2 And Target.Row > 1 Then
    Application.EnableEvents = False
    If Target.Offset(0, -1) <> "" Then
        If Target <> "" Then
            Target.Offset(0, 1).Value = Application.Count(Range("B:B"))
        Else
            Target.Offset(0, 1).Value = ""
        End If
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

    To test the code, input qty in column B in the attached and see if column C gets populated with the desired output.

     

     

3 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Oct 15, 2020

    subash758 

    I have used VBA to achieve the desired output.

    Right click on Sheet1 Tab --> View Code to see the code.

    The code is for Sheet Change Event so that once you input a qty in column B and if the corresponding cell in column A is not empty it would place the desired order sequence# in corresponding cell in column C.

    Code:

    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
If Target.Column = 2 And Target.Row > 1 Then
    Application.EnableEvents = False
    If Target.Offset(0, -1) <> "" Then
        If Target <> "" Then
            Target.Offset(0, 1).Value = Application.Count(Range("B:B"))
        Else
            Target.Offset(0, 1).Value = ""
        End If
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

    To test the code, input qty in column B in the attached and see if column C gets populated with the desired output.

     

     

Resources