Sequence to start and resume n rows down

[keef2]

Hello,

Hoping for a solution to my challenge. I am trying to automatically format the sequence so that it starts at 1 and increased by 1 on sheet 2 to a max number of cell K19 on a sheet 1. Where it becomes a little more complex is I was hoping to have the sequence start in F5 on delivery schedule start at 1 go to 5 columns then drop down to F16 and resume the sequence till the max number is met from K19 on user input. I would always like it to have 5 columns for each level of sequence if that makes sense. Thanks in advance

load list prototype Working.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
5	Summary	QTY	Unit	Size	Notes/Vendor	Delivery #1	Delivery #2	Delivery #3	Delivery #4	Delivery #5	Delivery #6	Delivery #7	Delivery #8	Delivery #9	Delivery #10	Delivery #11	Delivery #12	Delivery #13
6	Pressure Treated Wood	2,600	LF	2x
7	Pressure Treated Wood - w/ shim	510	LF	2x
8	Roof Hatch	1	EA	30"x96"x5.25"
9	Sumps	10	EA	8x8
10	Crickets	5	EA
11	Expansion Joint - Backer Rod	510	LF
12	Expansion Joint - Batt Insulation	510	LF
13	Expansion Joint - EPDM	510	LF
14	Froth Pak	15	EA
Delivery Schedule

Cell Formulas
Range	Formula
A5:E14	A5	=IF(FILTER('User Input '!M31:Q43,'User Input '!N31:N43>0)=0,"",FILTER('User Input '!M31:Q43,'User Input '!N31:N43>0))
F5:R5	F5	=IFERROR("Delivery #"&SEQUENCE(,'User Input '!K19,1,1),"")
Dynamic array formulas.

 

[keef2]

To be clear whatever K19 on sheet 1 is equal to in this example 13 i would like it to be similar format to this: Note K19 can be any number. Lastly, I don't know what the best way to handle this whether sequence & offset or match & index but really not to familiar with writing this complex formula. Or if there is a VBA code that is easier I'm open to that just want it to run automatically if K19 on sheet 1 contains a value. Again hope this makes sense and thanks again.

load list prototype Working.xlsx
	A	B	C	D	E	F	G	H	I	J
5	Summary	QTY	Unit	Size	Notes/Vendor	Delivery 1	Delivery 2	Delivery 3	Delivery 4	Delivery 5
6	Pressure Treated Wood	2,600	LF	2x
7	Pressure Treated Wood - w/ shim	510	LF	2x
8	Roof Hatch	1	EA	30"x96"x5.25"
9	Sumps	10	EA	8x8
10	Crickets	5	EA
11	Expansion Joint - Backer Rod	510	LF
12	Expansion Joint - Batt Insulation	510	LF
13	Expansion Joint - EPDM	510	LF
14	Froth Pak	15	EA
15
16						Delivery 6	Delivery 7	Delivery 8	Delivery 9	Delivery 10
17
18
19
20
21
22
23
24
25
26
27						Delivery 11	Delivery 12	Delivery 13
28
29
30
31
32
33
34
35
36
Delivery Schedule

Cell Formulas
Range	Formula
A5:E14	A5	=IF(FILTER('User Input '!M31:Q43,'User Input '!N31:N43>0)=0,"",FILTER('User Input '!M31:Q43,'User Input '!N31:N43>0))
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Will you be looking to enter information in F6:J14?

 

[keef2]

Hi & welcome to MrExcel.
Will you be looking to enter information in F6:J14?

Thank you! Yes, I would like user inputs in those cells.

 

[Fluff]

In that case you will need to put a separate formula into each row.

 

[keef2]

In that case you will need to put a separate formula into each row.

Im ok with that but what would that formula look like? is it similar to the sequence formula i already used? What i dont understand is say K19 = 4 i only want to see sequence 1 thru 4. However if K19=21 or whatever i would want it to continue down till it reaches K19 value. Thanks again!

 

[Fluff]

How about like

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1						8
2
3
4
5						Delivery 1	Delivery 2	Delivery 3	Delivery 4	Delivery 5
6
15
16						Delivery 6	Delivery 7	Delivery 8
17
26
27
28
29
Database

Cell Formulas
Range	Formula
F5:J5	F5	="Delivery "&SEQUENCE(,MIN(5,F1))
F16:H16	F16	=LET(a,F1-5,IF(a<=0,"","Delivery "&SEQUENCE(,MIN(5,a),6)))
F27	F27	=LET(a,F1-10,IF(a<=0,"","Delivery "&SEQUENCE(,MIN(5,a),11)))
Dynamic array formulas.

 

[keef2]

How about like

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1						8
2
3
4
5						Delivery 1	Delivery 2	Delivery 3	Delivery 4	Delivery 5
6
15
16						Delivery 6	Delivery 7	Delivery 8
17
26
27
28
29
Database

Cell Formulas
Range	Formula
F5:J5	F5	="Delivery "&SEQUENCE(,MIN(5,F1))
F16:H16	F16	=LET(a,F1-5,IF(a<=0,"","Delivery "&SEQUENCE(,MIN(5,a),6)))
F27	F27	=LET(a,F1-10,IF(a<=0,"","Delivery "&SEQUENCE(,MIN(5,a),11)))
Dynamic array formulas.

you are a rock star thanks!

 

[Fluff]

You're welcome & thanks for the feedback.