Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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 -- Optitro ----------------------------------------------------------------------- Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672 View this thread: http://www.excelforum.com/showthread.php?threadid=47847 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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" wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Documnet Number
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. Jim May Wrote: 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 i 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 Do # used. HTH Jim "Optitron" wrote in message ... 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 las 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 -- Optitro ----------------------------------------------------------------------- Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672 View this thread: http://www.excelforum.com/showthread.php?threadid=47847 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif number is greater than a number but less than another number | Excel Discussion (Misc queries) | |||
How can I click on a telephone number in an Excel 2002 spreadsheet, and have the number dialed? | Excel Discussion (Misc queries) | |||
How to calculate number of occurencies of a specific number number | Excel Discussion (Misc queries) | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
Poking Excel data into a Word Documnet | Excel Programming |