Documnet Number

[Optitron]

I have document numbers (doc#) in this format 5298T800. The 5298 changes each day, julian date. The last 2 digits go from 00 to 99 for each document. I have a column with these document numbers assigned to each order I make. I would like at the top of that column to know which document number would be next. So basically it should find the highest doc# and tell me what the next number should be, i.e. 5289T801. The 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(D2:D500),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.

--
Regards,
Tom Ogilvy

"Optitron" <[email protected]> wrote in
message news:[email protected]...
>
> I have document numbers (doc#) in this format 5298T800. The 5298 changes
> each day, julian date. The last 2 digits go from 00 to 99 for each
> document. I have a column with these document numbers assigned to each
> order I make. I would like at the top of that column to know which
> document number would be next. So basically it should find the highest
> doc# and tell me what the next number should be, i.e. 5289T801. The
> 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.
>
>
> --
> Optitron
> ------------------------------------------------------------------------
> Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
> View this thread: http://www.excelforum.com/showthread...hreadid=478472
>

[Jim May]

With Range D2:D10 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:D,COUNTA(D2:D10000)+1),2))+1,"00")


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

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> assuming the data starts in D2 and this formula is in D1
>
> =MAX(VALUE(RIGHT(OFFSET(D2,0,0,COUNTA(D2:D500),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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Optitron" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> I have document numbers (doc#) in this format 5298T800. The 5298 changes
>> each day, julian date. The last 2 digits go from 00 to 99 for each
>> document. I have a column with these document numbers assigned to each
>> order I make. I would like at the top of that column to know which
>> document number would be next. So basically it should find the highest
>> doc# and tell me what the next number should be, i.e. 5289T801. The
>> 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.
>>
>>
>> --
>> Optitron
>> ------------------------------------------------------------------------
>> Optitron's Profile:
> http://www.excelforum.com/member.php...o&userid=26729
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=478472
>>
>
>

[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.

--
Regards,
Tom Ogilvy




"Optitron" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> Optitron
> ------------------------------------------------------------------------
> Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
> View this thread: http://www.excelforum.com/showthread...hreadid=478472
>

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> Optitron
> ------------------------------------------------------------------------
> Optitron's Profile:
> http://www.excelforum.com/member.php...o&userid=26729
> View this thread: http://www.excelforum.com/showthread...hreadid=478472
>

[Optitron]

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> Optitron
> ------------------------------------------------------------------------
> Optitron's Profile:
> http://www.excelforum.com/member.php...o&userid=26729
> View this thread: http://www.excelforum.com/showthread...hreadid=478472
>