Formula: Start new number where last number stops in a range

[bjnockle]

Would like a formula to start a new number where the last number ends in a range. The number that will change from time to time is Ticket Serial Number. The ticket number is entered in cell A2 and B2 and concatenated in cell E2 (for example, 6723 - 238-248).

Ticket #: 6723 - (238-242)

I. when you entered a text value in A5, the ticket number is entered in B5.
For example,
A5: Baseball, B5: 6723-238
A6: Baseball, B6: 6723-239
A7: Baseball, B7: 6723-240
A8: Baseball, B8: 6723-241
A9: Baseball, B9: 6723-242

II. With the ticket range exhausted, a new ticket number is entered in A2 and B2. Goal is to have the new ticket number start below A9 (that is A10)
A2: 4567 - (501-505)

A10: Baseball, B10: 4567-501
A11: Baseball, B11: 4567-502
A12: Baseball, B12: 4567-503
A13: Baseball, B13: 4567-504
A14: Baseball, B14: 4567-505

Sample file attached.

[Kaper]

I dont think you have shown any idea how spreadsheet could recognize the event: "ticket range exhausted" neither how shall be new first part and new second part starting number retrieved.

So as there is not enough data for "fully automatic" approach, then may be "semiautomatic" approach, will do.

The A2:B2 values are just reminders for the person using the sheet

And you can use for instance such formula in B6

Please Login or Register  to view this content.

and copy it down

Once the range is exhausted write new ticket number manually (remember to have the formula copied down before) - for instance in your B9 write 2567-756 and B10 etc will follow this new pattern

[bjnockle]

Kaper: I tried the formula you proposed and it did not work. If are you able to setup a simple approach for the solution I am looking for, I will take it. I am open to a solution that works.

Thanks

[Kaper]

Sorry to hear so, but ... it works. See attached file.
The formula is in column B
as I wrote - you gave no clue why
2387-115 shall be followed by 2567-756
From the data in the sheet it could be only noticed that there sholud be some change (values in row 2)

So it works as I tried to describe: as new events are written into column A there comes such moment when the range previous range is exhausted, Operator writes manually first number RRR-XXX in Bn cell
and when new name is entered in An+1 cell the cell Bn+1 shaows new ticket number RRR-XXX+1

Try writing Ice-yachting in A17, 4578-327 will appear in B17. Then write write Wrestling in A18 and 4578-328 will appear.
If we assume that it is already after current range, just write in B18 first ticket from new range, like 1234-432*
Then when you write in A19 Short-track in B19 will appear 1234-433.

That's it


* If I were operator I would check the whole pile of new tickets and write in row 2 what ticket numbers are available in current range.