Moving to the next cell when the value in the previous cell is reached

[typody]

Hi,

I am trying to batch users for a migration, and I want to create a formula that assigns these batches automatically based on batch size. Once the batch size is reached the batch value will change to the next batch, 1,2,3 etc etc

For example

AO Migration Schedule v1.xlsm
	A	B	C	D	E	F	G	H	I	J
4				Early Adopters
5			Date	13-Nov	15-Nov	26-Nov	27-Nov	28-Nov
6			Batch	1	2	3	4	5
7	Team	Size	Batch Size	5	10	20	50	150	Running Total	Batch Number
8	Team 1	1							1	1
9	Team 2	2							3	1
10	Team 3	3							6	2
11	Team 4	4							10	2
12	Team 5	5							15	3
13	Team 6	6							21	3
14	Team 7	7							28	3
15	Team 8	8							36	4
16	Team 9	9							45	4
17	Team 10	10							55	4
18	Team 11	11							66	4
19	Team 12	12							78	4
20	Team 13	13							91	5
21	Team 14	14							105	5
22	Team 15	15							120	5
23	Team 16	16							136	5
24	Team 17	17							153	5
25	Team 18	18							171	5
26	Team 19	19							190	5
27	Team 20	20							210	5
28	Team 21	21							231	5
Sheet1

Cell Formulas
Range	Formula
I8	I8	=B8
I9:I28	I9	=SUM($B$8:B9)

Here I have manually added the batch number but I would like it to auto increment when the number of moves exceeds the previous batch capacity. Does that makes sense? I hope so.

 

[Muhammad_Usman]

24-08-21 Exp.xlsx
	A	B	C	D	E	F	G	H	I	J	K
6				1	2	3	4	5
7	team	size		5	10	20	50	150		Running Total
8	Team1	1								1	1
9	Team 2	2								3	1
10	Team3	3								6	2
11	Team4	4								10	2
12	Team5	5								15	2
13	Team6	6								21	3
14	Team7	7								28	3
15	Team8	8								36	3
16	Team9	9								45	3
17	Team10	10								55	4
18	Team11	11								66	4
19	Team12	12								78	4
20	Team13	13								91	4
Sheet1

Cell Formulas
Range	Formula
J8	J8	=B8
K8:K20	K8	=INDEX(D$6:H$6,MATCH((J8+4),$D$7:$H$7,1))
J9:J20	J9	=SUM($B$8:B9)

 

[typody]

Thanks, I will give it a shot

 

[typody]

well that turned out easier that I expected. Thanks for the help. As usual I was making things far to complex

 

[typody]

Quick question. What is the +4 in the formula used for? I am just trying to recreate it on a larger scale.

 

[typody]

It looks like things go wrong when the batch sizes are in double (or triple) figures

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3
4				Early Adopters
5			Date	13/11/2021	15/11/2021	26/11/2021	27/11/2021	28/11/2021
6			Batch	1	2	3	4	5
7	Team	Size	Batch Size	5	10	20	50	150	x	Batch Number
8	Team 1	1								1
9	Team 2	2								1
10	Team 3	3							x	2
11	Team 4	4								2
12	Team 5	5							x	3
13	Team 6	6								3
14	Team 7	7								3
15	Team 8	8							x	4
16	Team 9	9								4
17	Team 10	10								4
18	Team 11	11								4
19	Team 12	12								4
20	Team 13	13							x	5
21	Team 14	14								5
22	Team 15	15								5
23	Team 16	16								5
24	Team 17	17								5
25	Team 18	18								5
26	Team 19	19								5
27	Team 20	20								5
28	Team 21	18								5
Lists

Cell Formulas
Range	Formula
I8:I28	I8	=IF(SUM(INDEX(B$1:B8,LOOKUP(2,1/(I$7:I7="x"),ROW(I$7:I8))):B8)>INDEX($D$7:$H$7,COUNTIFS(I$7:I7,"x")),"x","")
J8:J28	J8	=INDEX($D$6:$H$6,COUNTIFS(I$7:I8,"x"))

 

[typody]

Cheers, I will try that one. Seems complicated though, might take me a bit to figure out how it works