Documnet Number

[Optitron]

I have document numbers (doc#) in this format 5298T800. The 5298 change
each day, julian date. The last 2 digits go from 00 to 99 for eac
document. I have a column with these document numbers assigned to eac
order I make. I would like at the top of that column to know whic
document number would be next. So basically it should find the highes
doc# and tell me what the next number should be, i.e. 5289T801. Th
julian date doesn't need to change unless you know how to program it
It will need to start over at zero when it gets to 99

 

[Tom Ogilvy]

assuming the data starts in D2 and this formula is in D1

=MAX(VALUE(RIGHT(OFFSET(D2,0,0,COUNTA(D2500),1))))

entered with Ctr+Shift+Enter rather than just enter since this is an array
formula.

It isn't clear whether you wanted the highest value for the highest julian
date.

 

[Jim May]

With Range D210 containing

5298T800
5298T801
5298T802
5298T803
5298T804
5298T805
5299T800
5299T801
5299T802


The current formula yeilds 5
where I think the OP wants 3

My expanded sample included 2 digit doc #;s 13,25
so I needed to enter the ,2 to complete the Right(value,numchar).

I came up with entering in cell D1
=TEXT(VALUE(RIGHT(INDEX(D,COUNTA(D210000)+1),2))+1,"00")


yeilds 03
Not sure if this is what is wanted or not,,,
FWIW,,

 

[Optitron]

OK, Jim, your's resulted in 71. Tom, your's resulted in #VALUE!. Here's
more info, The doc#s start in BW9. The julian date will change each
day, so as long as the result says T8XX, I will be happy, unless Julian
date works in Excel. There is also text in some of the cells in that
column when there isn't a doc#, just in case that would conflict. The
doc#s that are in that column are not in order.

This is what I need but don't know how to write it:
Take the highest value of the first four digits and the highest value
of the last two digits and tell me what the next two digits will be. I
just need this in one cell that I have frozen at the top of the page.

5275T800
5275T801
~
5298T898
5299T899
Then next number will be T800 (i'll be able to input the Julian Date)

The reason for all this is that I have to use another application that
uses the doc#s and when I need to order something I have to scan
through Column BW to find out which doc# I need to use next so they are
in sequence.

 

[Tom Ogilvy]

If you are using this formula to tell you what the next document number
should be and you are entering that in the next blank cell in the column,
then why wouldn't your numbers be in order. Jim's formula looks at the last
cell in the column and gives you the number of that. That is certainly the
simplest.

My formula picked the highest number in the column but assumed you wouldn't
have values in the column that didn't fit your description - it also didn't
pay attention to julian date.

One thing you might look at is adding a little discipline to how you have
your data organized, then you might be able to set up a simple formula to
give you what you want.

 

[Optitron]

I guess it is alittle more complicated than it should be. I have the
database organized by Report number. Basically when someone breaks a
tool they do a report and then I order the tool with a doc#. I just
wanted a cell in the frozen area at the top to say what the last doc#
was so that when I make my next order I don't have to scroll through
100+ rows. What i'll end up doing is just typing the last doc# into
that cell manually.

 

[Jim May]

Ok,
If it doesn't conflict with other code you might have going,
you could add the follwing code in the specific Worksheet
module Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
lrow = Range("E65536").End(xlUp).Row 'assumes your doc# info is kept
in Column E
Range("e1").Value = Range("e" & lrow).Value
End Sub

This way after any and devery change that occurs in your worksheet
your Cell (in this case (E1) is always "up-to-date" with the last Doc #
used.

HTH
Jim

 

[Optitron]

That code seems to give me only the last entry in that column, not th
last doc# I typed in. Sometimes I need to change earlier doc#s in tha
column.