Show Next Available Number

[kwac01]

Hello, I am trying to determine how I can display my next available folio number on a spreadsheet. The numbers start at 2018-001 and go indefinitely until the year is over and then start at 2019-001 and go indefinitely until the end of the year. I found the formula below on another thread and it has the basic idea but it is trying to start at 1. I am also unsure how to adjust it because my folio numbers have "-" in them. Does anyone know if what I am trying to do is possible?

=IFERROR(INDEX(ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))),MATCH(0,INDEX(COUNTIF(A:A,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))),),0)),MAX(A:A)+1)

[6StringJazzer]

If your numbers are all in column A this will give you the next available number for the current year. I am going from your first and second sentences. I have no idea what that formula you show is supposed to be doing.

Please Login or Register  to view this content.

[kwac01]

Thank you! Any idea why it's showing me my next available number is 2018-308 even though I've used that number already? I've confirmed there are no typos in the spreadsheet or formula. If I add another 2018-308 to the spreadsheet it will show the next available is 2018-309, again though, this number also already has been used.

[kwac01]

I've narrowed down the issue. It's because the very last number entered in column A is 2018-307. Is there a way to adjust the formula to show the next available number in the whole column? The numbers are not in numerical order because I keep them organized by date.

[FDibbins]

Because those cells contain text, not a real number, excel cannot easily determine what the highest have would be. Would you consider adding a helper column to pull out the "number"? This would simplify everything.

[Zer0Cool]

Could you post a sample file, it would make it much easier to provide help.

To make sure I understand:

There are 2 parts to this folio number: year-sequence #.

We want the sequence number to increment +1 to always be 1 more than the greatest previous in the same folio number combination of year-sequence? IE: 2017-001, 2018-001...2017-002, 2018-002, right?

Is the sequence number always 3 digits or x number of digits or can we use real numbers? IE: 001, 002, ... 099 ... 999 vs 1, 2, ... 99 ... 999

Can we get the year automatically from another cell in the same row or do we need to manually determine the year to use (either using current year or a static year)?

[kwac01]

Sure, I have attached a sample.

You are correct that there are two parts to the folio, the year and then a number. It is always a 3 digit number (001, 002, 003). I want to determine what the next available number to use would be by going up in increments of 1. There is not currently a place to pull the year from but I can easily add that. Additionally, some folios are entered more than once and the numbers are not in numerical order.

[FDibbins]

Would you consider using a helper column?

I used this in col E...
=--SUBSTITUTE(A3,"-","")
copied down.

Then to get the next number...
=LEFT(MAX(E3:E26),4)&"-"&TEXT(RIGHT(MAX(E3:E26),3)+1,"000")

[Zer0Cool]

Additionally, some folios are entered more than once

Could you clarify what you mean here? I was under the impression you wanted to generate folio numbers, if the folio numbers are generated what dictates if they are repeated or unique?

To be sure, you are not trying to both manually enter new folio numbers AND generate them automatically too, right?

[kwac01]

Yes, I can enter a helper column. I have also attached a better sample sheet that looks much more like what I am using. I would probably use Column O for my helper column. The formula would go in E2

[kwac01]

Currently, I have to manually determine what my next folio number should be and type it in in the next available row. I have been coming across an issue where I think I have the next available number but I ended up using one that was used before. So I'm trying to put a formula in a cell which will tell me what my actual next available to use would be.


They are sometimes entered more than once because if there is any new information attached to that file, I enter a new row with the new info. This is for money coming in an out on a file. For example, for file number 2017-001 I received $500, that is entered into a row. 4 weeks later, I give someone $300 from that file, that is entered on a new row with the same folio number of 2017-001. BUt I may have already enters a bunch of other folios in between those rows. When I have a brand new file, I am trying to determine what my next available folio to use would be.

[FDibbins]

Chane the formula in O to this...
=IF(A4="","",--SUBSTITUTE(A4,"-",""))
copied down

You can hide this column if you want to

[FDibbins]

Actually, here is a much shorter way, without using the helper.

E2=SUMPRODUCT(MAX(--SUBSTITUTE($A$4:$A$34,"-","")))+1
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[kwac01]

Love it! But it doesn't update as I start entering new folios in new rows without having to go in an adjust the formula range every time. If I change the range to a much higher number, say 1000, without actaully having had entered any info in those rows, I get the #VALUES! error. See new attached spreadsheet with new rows added and E2=SUMPRODUCT(MAX(--SUBSTITUTE($A$4:$A$34,"-","")))+1. Notice it says my next available folio is 2018016 but it should actually be 2018018.

[FDibbins]

It doesnt like the empty cells

Try this instead, it creates a dynamic range (still CSE)
=SUMPRODUCT(MAX(--SUBSTITUTE(OFFSET($A$1,3,0,COUNTA($A:$A)-2,1),"-","")))+1

[kwac01]

SCORE! That's a win, thank you!

[FDibbins]

Good stuff. Happy to help and thanks for the feedback

(Dont forget to remove that helper column if you dont need it)

[k1w1sm]

I see you have solved this already, however, I have an alternative solution which might as well have.

It is a macro on the sheet rather than in the module and it fires every cell selection but only does stuff to empty cells in column 1.

It gives you the option to specify the year

Even if you don't like it you may be able to use the technique for something else.

[FDibbins]

k1w1sm hanks for sharing your suggestion

Just because a thread is resolved, doesnt mean there are not any other/better suggestions out there

[kwac01]

Hello everyone! This formula has been helpful for almost 8 months now. Today, I opened my spreadsheet, copied the formula to give to a coworker for her spreadsheet and now the formula does not work for her or me. I now get the #VALUE! error message. I noticed that if I open the old example spreadsheet I used in this post, double click in the formula box and press enter, without changing anything, the formulas all revert to the #VALUE! error message. Has something changed in excel that it no longer likes these formulas? Here is exactly what I currently have in my formula box, any ideas? =SUMPRODUCT(MAX(--SUBSTITUTE(OFFSET($A$1,3,0,COUNTA($A:$A)-2,1),"-","")))+1

[6StringJazzer]

Sounds like you are not entering it as an array formula. Recall the direction given by FDibbins earlier:

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[kwac01]

Oh wow, derp. -__- Thank you!