MAX A query
Thread Starter
Join Date: Oct 2000
Location: Sunny Sussex
Posts: 778
Likes: 0
Received 0 Likes
on
0 Posts
MAX A query
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.
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.
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
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.
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
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.
Let the count continue above 10,000 but subtract 10,000 when displaying.
Thread Starter
Join Date: Oct 2000
Location: Sunny Sussex
Posts: 778
Likes: 0
Received 0 Likes
on
0 Posts
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.
