MAX A query

[Parapunter]

Hi all, can't find this on a search, sooo,

I have a spreadsheet that I use for business. Within it, each job is allocated a unique job reference & this is calculated sequentially by using MAXA (range)+1 so that the formula finds the highest value in the range & adds 1 to give the next order number.

Thing is, this morning I came up order no. 10,000 & I would like to reset it to one. Obviously MAXA won't do that, so is there a way, bearing in mind that if I reset the range, the high 999x job numbers are mixed on the sheet with the desired 1, 2 range etc. because the sheet is necessarily ordered by date rather than job number.

[Keef]

The only idea I can come up with is to keep going at 10,000 plus, but subtract 10,000 from the number displayed if it's >10,000.

[Parapunter]

Thanks Keef. =MAXA(range)-9999! Duh!!! I think I may have left my brain on the bus.

[Keef]

I suspect that may give you an answer based on entry number 9999 or 10000 for each new entry because the next entry won't trigger MAXA.

Let the count continue above 10,000 but subtract 10,000 when displaying.

[Parapunter]

Actually, it didn't work quite as well as I had hoped, so what I've done as a workaround is to shift the range to count from around five, which is about where the new numbers start to become sequential. That works but it is dirty & would fail if say job 9987 was rebooked into next week for example.