Inventory numbers - Consecutive, but out of sequence

[Guest]

I'm trying to create a spreadsheet that will automatically input a new item
number when I enter a new item on a list. Easy enough, but the catch is I
want the item number to remain assoicated with the item regardless of sorting.

In the classic Excel Help file manner I will explain what I want.

Current on screen data
A\B\C
1\Oranges\52
2\Apples\34
3\Bananas\97

If I sort by column B:
A\B\C
2\Apples\34
3\Bananas\97
1\Oranges\52

Now I add another item (cherries\63) to the next available row (B5). I want
my output to look like this: (Note the number 4 is automated)
A\B\C
2\Apples\34
3\Bananas\97
1\Oranges\52
4\Cherries\63

Any ideas? I've seen lots of consecutive number ideas, but never one like
this. Please help.

 

[Jason Morin]

Click on the worksheet, right-click and select View Code,
and paste in the macro below. It triggers off an entry
into B column:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim RngAbov As Range
Dim MaxVal As Variant

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
Exit Sub

Set RngAbov = Me.Range("a1", .Offset(-1, 0))

MaxVal = Application.Max(RngAbov)

Application.EnableEvents = False
.Offset(0, -1).Value = MaxVal + 1
.Offset(1, 0).Select
Application.EnableEvents = True

End With

End Sub

 

[JE McGimpsey]

One way:

Put this in your Worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 2 Then
With .Offset(0, -1)
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = Application.Max(.EntireColumn) + 1
Application.EnableEvents = True
End If
End With
End If
End With
End Sub


If you're unfamiliar with macros, see David McRitchie's "Getting started
with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Jason,

Thanks for the prompt response! This worked great after I figured out that
the "Exit Sub" was not on the same line as the Then statement.

I also changed the RngAbov to "A:A" so as to include all rows. I have some
items that are seperate from others.

The only other thing I noticed is that if I hit delete in a B cell that has
no inventory number it still creates one. I can live with it, but if you
have a quick fix I'd love to have it.

Thanks again.

 

[Jason Morin]

Try:

If Intersect(.Cells, Me.Range("B1:B10")) = "" Then Exit
Sub

(one line)

underneath the 1st intersect line.

Jason