How to Increment Excel cell reference by any number

[ArranI]

Hello,

Hopefully a simple question for those much more advanced than I.

I'm looking to determine a way of referencing a cell range which increases in increments of 1, however the range that is to show the data increases in increments of 6.

i.e.
A2 to equal L2
A7 to equal L3
A12 to equal L4
A17 to equal L5

The example file below is a simplified aspect of what I'm after, with only 4 Part#s.
The actual file, contains hundreds which is why a manual option is not feasible.

Would really appreciate any insights into how best to solve this.

Thank you!

AI - Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Part#	Description	Leadtime	Division	W1	W2	W3					Part#	Description	Leadtime
2	124578	Nut	20	Balance	5	35	30					124578	Nut	20
3	124578	Nut	20	Stock	20	5	35					134679	Pin	20
4	124578	Nut	20	Transit		50						215487	Lever	35
5	124578	Nut	20	- North	5	0	5					235689	Bolt	30
6	124578	Nut	20	- South	10	20	0
7	134679	Pin	20	Balance	-10	2	-11
8	134679	Pin	20	Stock	0	-10	2
9	134679	Pin	20	Transit		20
10	134679	Pin	20	- North	2	0	5
11	134679	Pin	20	- South	8	8	8
12	215487	Lever	35	Balance	10	40	85
13	215487	Lever	35	Stock	50	10	40
14	215487	Lever	35	Transit		50	50
15	215487	Lever	35	- North	30	0	5
16	215487	Lever	35	- South	10	20	0
17	235689	Bolt	30	Balance	-15	25	20
18	235689	Bolt	30	Stock	0	-15	25
19	235689	Bolt	30	Transit		60
20	235689	Bolt	30	- North	5	0	5
21	235689	Bolt	30	- South	10	20	0
Sheet1

Cell Formulas
Range	Formula
E17:G17,E12:G12,E7:G7,E2:G2	E2	=(SUM(E3:E4)-(SUM(E5:E6)))
F3:G3,F18:G18,F13:G13,F8:G8	F3	=E2
A3:C6,A18:C21,A13:C16,A8:C11	A3	=A2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:G21	Cell Value	<0	text	NO

 

[Joe4]

Welcome to the Board!

You can place this formula in cell A2 and copy down:

=INDIRECT("L" & INT((ROW()+8)/5))

 

[Fluff]

Hi & welcome to MrExcel.
Another option in A2 only

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Part#	Description	Leadtime	Division	W1	W2	W3					Part#	Description	Leadtime
2	124578	Nut	20	Balance	5	35	30					124578	Nut	20
3	124578	Nut	20	Stock	20	5	35					134679	Pin	20
4	124578	Nut	20	Transit		50						215487	Lever	35
5	124578	Nut	20	- North	5	0	5					235689	Bolt	30
6	124578	Nut	20	- South	10	20	0
7	134679	Pin	20	Balance	-10	2	-11
8	134679	Pin	20	Stock	0	-10	2
9	134679	Pin	20	Transit		20
10	134679	Pin	20	- North	2	0	5
11	134679	Pin	20	- South	8	8	8
12	215487	Lever	35	Balance	10	40	85
13	215487	Lever	35	Stock	50	10	40
14	215487	Lever	35	Transit		50	50
15	215487	Lever	35	- North	30	0	5
16	215487	Lever	35	- South	10	20	0
17	235689	Bolt	30	Balance	-15	25	20
18	235689	Bolt	30	Stock	0	-15	25
19	235689	Bolt	30	Transit		60
20	235689	Bolt	30	- North	5	0	5
21	235689	Bolt	30	- South	10	20	0
22
Sheet6

Cell Formulas
Range	Formula
A2:C21	A2	=LET(f,FILTER(L2:N100,L2:L100<>""),r,ROWS(f)*5,INDEX(f,CEILING.MATH(SEQUENCE(r)/5,1),{1,2,3}))
E17:G17,E12:G12,E7:G7,E2:G2	E2	=(SUM(E3:E4)-(SUM(E5:E6)))
F3:G3,F18:G18,F13:G13,F8:G8	F3	=E2
Dynamic array formulas.

 

[ArranI]

Welcome to the Board!

You can place this formula in cell A2 and copy down:

=INDIRECT("L" & INT((ROW()+8)/5))

Hi Joe,

Thanks for replying so quickly and for the kind welcome!

That works great for the simplified version I provided.
Trying to extrapolate it to the larger data set I'm working with (with many more rows) however results in an issue which I'm guessing is due to the increasing INT value exceeding the boundary for the next integer up.
Additionally for the larger data set, the data source to pull from is on another sheet, I'm not sure how this combines with the INDIRECT formula, however if you know then I'd appreciate if you could let me know.

I will re-embed the sheets with the actual number of rows for each Part#.

Could you enlighten me on how you derived the ROW()+8 / 2 ratio, so that I can apply the same logic for future?

AI - Example.xlsx
	A	B	C	D	E	F	G
1	Part#	Description	Leadtime	Division	W1	W2	W3
2	124578	Nut	20	Balance	5	35	30
3	124578	Nut	20	Stock	20	5	35
4	124578	Nut	20	Transit		50
5	124578	Nut	20	- North	5	0	5
6	124578	Nut	20	- South	10	20	0
7	124578	Nut	20	- A
8	124578	Nut	20	- B
9	124578	Nut	20	- C
10	124578	Nut	20	- D
11	124578	Nut	20	- E
12	124578	Nut	20	- F
13	124578	Nut	20	- G
14	124578	Nut	20	- H
15	124578	Nut	20	- I
16	124578	Nut	20	- J
17	134679	Pin	20	Balance	-10	2	-11
18	134679	Pin	20	Stock	0	-10	2
19	134679	Pin	20	Transit		20
20	134679	Pin	20	- North	2	0	5
21	134679	Pin	20	- South	8	8	8
22	134679	Pin	20	- A
23	134679	Pin	20	- B
24	134679	Pin	20	- C
25	134679	Pin	20	- D
26	134679	Pin	20	- E
27	134679	Pin	20	- F
28	134679	Pin	20	- G
29	134679	Pin	20	- H
30	134679	Pin	20	- I
31	134679	Pin	20	- J
32	215487	Lever	35	Balance	10	40	85
33	215487	Lever	35	Stock	50	10	40
34	215487	Lever	35	Transit		50	50
35	215487	Lever	35	- North	30	0	5
36	215487	Lever	35	- South	10	20	0
37	215487	Lever	35	- A
38	215487	Lever	35	- B
39	215487	Lever	35	- C
40	215487	Lever	35	- D
41	215487	Lever	35	- E
42	215487	Lever	35	- F
43	215487	Lever	35	- G
44	215487	Lever	35	- H
45	215487	Lever	35	- I
46	215487	Lever	35	- J
47	235689	Bolt	30	Balance	-15	25	20
48	235689	Bolt	30	Stock	0	-15	25
49	235689	Bolt	30	Transit		60
50	235689	Bolt	30	- North	5	0	5
51	235689	Bolt	30	- South	10	20	0
52	235689	Bolt	30	- A
53	235689	Bolt	30	- B
54	235689	Bolt	30	- C
55	235689	Bolt	30	- D
56	235689	Bolt	30	- E
57	235689	Bolt	30	- F
58	235689	Bolt	30	- G
59	235689	Bolt	30	- H
60	235689	Bolt	30	- I
61	235689	Bolt	30	- J
Sheet1

Cell Formulas
Range	Formula
E47:G47,E32:G32,E17:G17,E2:G2	E2	=(SUM(E3:E4)-(SUM(E5:E16)))
F3:G3,F48:G48,F33:G33,F18:G18	F3	=E2
B3:C16,B48:C61,B33:C46,B18:C31	B3	=B2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:G9,E17:G24,E32:G39,E47:G54	Cell Value	<0	text	NO

AI - Example.xlsx
	A	B	C
1	Part#	Description	Leadtime
2	124578	Nut	20
3	134679	Pin	20
4	215487	Lever	35
5	235689	Bolt	30
DATA SOURCE

 

[Joe4]

an issue which I'm guessing is due to the increasing INT value exceeding the boundary for the next integer up.

This changes the question COMPLETELY, and is a good example of why you should NEVER over-simplify your question. You had made it sound like it was a static increment.
When you do that, you risk getting an answer that works for the exact question and example your originally posted, but not for your actual situation (because you mis-represented the actual situation to us).

If the sections may be different sizes, how do you propose we identify that?
Can we look in column D, and every time the word "Balance" appears, it should move to the next part?
Is that a good indicator we can use?

 

[Fluff]

With the formula I suggested, that will spill cols A:C for all rows.

Fluff.xlsm
	A	B	C	D	E	F	G
1	Part#	Description	Leadtime	Division	W1	W2	W3
2	124578	Nut	20	Balance	5	35	30
3	124578	Nut	20	Stock	20	5	35
4	124578	Nut	20	Transit		50
5	124578	Nut	20	- North	5	0	5
6	124578	Nut	20	- South	10	20	0
7	124578	Nut	20	- A
8	124578	Nut	20	- B
9	124578	Nut	20	- C
10	124578	Nut	20	- D
11	124578	Nut	20	- E
12	124578	Nut	20	- F
13	124578	Nut	20	- G
14	124578	Nut	20	- H
15	124578	Nut	20	- I
16	124578	Nut	20	- J
17	134679	Pin	20	Balance	-10	2	-11
18	134679	Pin	20	Stock	0	-10	2
19	134679	Pin	20	Transit		20
20	134679	Pin	20	- North	2	0	5
21	134679	Pin	20	- South	8	8	8
22	134679	Pin	20	- A
23	134679	Pin	20	- B
24	134679	Pin	20	- C
25	134679	Pin	20	- D
26	134679	Pin	20	- E
27	134679	Pin	20	- F
28	134679	Pin	20	- G
29	134679	Pin	20	- H
30	134679	Pin	20	- I
31	134679	Pin	20	- J
32	215487	Lever	35	Balance	10	40	85
33	215487	Lever	35	Stock	50	10	40
34	215487	Lever	35	Transit		50	50
35	215487	Lever	35	- North	30	0	5
36	215487	Lever	35	- South	10	20	0
37	215487	Lever	35	- A
38	215487	Lever	35	- B
39	215487	Lever	35	- C
40	215487	Lever	35	- D
41	215487	Lever	35	- E
42	215487	Lever	35	- F
43	215487	Lever	35	- G
44	215487	Lever	35	- H
45	215487	Lever	35	- I
46	215487	Lever	35	- J
47	235689	Bolt	30	Balance	-15	25	20
48	235689	Bolt	30	Stock	0	-15	25
49	235689	Bolt	30	Transit		60
50	235689	Bolt	30	- North	5	0	5
51	235689	Bolt	30	- South	10	20	0
52	235689	Bolt	30	- A
53	235689	Bolt	30	- B
54	235689	Bolt	30	- C
55	235689	Bolt	30	- D
56	235689	Bolt	30	- E
57	235689	Bolt	30	- F
58	235689	Bolt	30	- G
59	235689	Bolt	30	- H
60	235689	Bolt	30	- I
61	235689	Bolt	30	- J
62
Sheet6

Cell Formulas
Range	Formula
A2:C61	A2	=LET(f,FILTER('DATA SOURCE'!A2:C100,'DATA SOURCE'!A2:A100<>""),r,ROWS(f)*15,INDEX(f,CEILING.MATH(SEQUENCE(r)/15,1),{1,2,3}))
E47:G47,E32:G32,E17:G17,E2:G2	E2	=(SUM(E3:E4)-(SUM(E5:E16)))
F3:G3,F48:G48,F33:G33,F18:G18	F3	=E2
Dynamic array formulas.

 

[ArranI]

This changes the question COMPLETELY, and is a good example of why you should NEVER over-simplify your question. You had made it sound like it was a static increment.
When you do that, you risk getting an answer that works for the exact question and example your originally posted, but not for your actual situation (because you mis-represented the actual situation to us).

If the sections may be different sizes, how do you propose we identify that?
Can we look in column D, and every time the word "Balance" appears, it should move to the next part?
Is that a good indicator we can use?

Hi Joe,

Noted for future.

In answer to your questions:
Proposed sheet would have same size for all sections.
If it were to have differing sizes, then yes, "Balance" would indicate the beginning of a new section.

 

[Rhodie72]

Hello,

Hopefully a simple question for those much more advanced than I.

I'm looking to determine a way of referencing a cell range which increases in increments of 1, however the range that is to show the data increases in increments of 6.

i.e.
A2 to equal L2
A7 to equal L3
A12 to equal L4
A17 to equal L5

The example file below is a simplified aspect of what I'm after, with only 4 Part#s.
The actual file, contains hundreds which is why a manual option is not feasible.

Would really appreciate any insights into how best to solve this.

Thank you!

This is a perfect example of what should be done using VBA UserForms. You can create a front-end application that is built in Excel and never see the spreadsheets. It would take time to develop but once completed it would be a custom designed parts ordering system.

 

[ArranI]

With the formula I suggested, that will spill cols A:C for all rows.

Fluff.xlsm
	A	B	C	D	E	F	G
1	Part#	Description	Leadtime	Division	W1	W2	W3
2	124578	Nut	20	Balance	5	35	30
3	124578	Nut	20	Stock	20	5	35
4	124578	Nut	20	Transit		50
5	124578	Nut	20	- North	5	0	5
6	124578	Nut	20	- South	10	20	0
7	124578	Nut	20	- A
8	124578	Nut	20	- B
9	124578	Nut	20	- C
10	124578	Nut	20	- D
11	124578	Nut	20	- E
12	124578	Nut	20	- F
13	124578	Nut	20	- G
14	124578	Nut	20	- H
15	124578	Nut	20	- I
16	124578	Nut	20	- J
17	134679	Pin	20	Balance	-10	2	-11
18	134679	Pin	20	Stock	0	-10	2
19	134679	Pin	20	Transit		20
20	134679	Pin	20	- North	2	0	5
21	134679	Pin	20	- South	8	8	8
22	134679	Pin	20	- A
23	134679	Pin	20	- B
24	134679	Pin	20	- C
25	134679	Pin	20	- D
26	134679	Pin	20	- E
27	134679	Pin	20	- F
28	134679	Pin	20	- G
29	134679	Pin	20	- H
30	134679	Pin	20	- I
31	134679	Pin	20	- J
32	215487	Lever	35	Balance	10	40	85
33	215487	Lever	35	Stock	50	10	40
34	215487	Lever	35	Transit		50	50
35	215487	Lever	35	- North	30	0	5
36	215487	Lever	35	- South	10	20	0
37	215487	Lever	35	- A
38	215487	Lever	35	- B
39	215487	Lever	35	- C
40	215487	Lever	35	- D
41	215487	Lever	35	- E
42	215487	Lever	35	- F
43	215487	Lever	35	- G
44	215487	Lever	35	- H
45	215487	Lever	35	- I
46	215487	Lever	35	- J
47	235689	Bolt	30	Balance	-15	25	20
48	235689	Bolt	30	Stock	0	-15	25
49	235689	Bolt	30	Transit		60
50	235689	Bolt	30	- North	5	0	5
51	235689	Bolt	30	- South	10	20	0
52	235689	Bolt	30	- A
53	235689	Bolt	30	- B
54	235689	Bolt	30	- C
55	235689	Bolt	30	- D
56	235689	Bolt	30	- E
57	235689	Bolt	30	- F
58	235689	Bolt	30	- G
59	235689	Bolt	30	- H
60	235689	Bolt	30	- I
61	235689	Bolt	30	- J
62
Sheet6

Cell Formulas
Range	Formula
A2:C61	A2	=LET(f,FILTER('DATA SOURCE'!A2:C100,'DATA SOURCE'!A2:A100<>""),r,ROWS(f)*15,INDEX(f,CEILING.MATH(SEQUENCE(r)/15,1),{1,2,3}))
E47:G47,E32:G32,E17:G17,E2:G2	E2	=(SUM(E3:E4)-(SUM(E5:E16)))
F3:G3,F48:G48,F33:G33,F18:G18	F3	=E2
Dynamic array formulas.

Hi Fluff,

Thanks very much for the solution.
Entered the formula into my sheet, and I'm replicating the results you're seeing.

I'll have a play around to see if I can decipher how/why it works.

Appreciate the help, thanks again!

 

[Joe4]

Hi Joe,

Noted for future.

In answer to your questions:
Proposed sheet would have same size for all sections.
If it were to have differing sizes, then yes, "Balance" would indicate the beginning of a new section.

Did fluff's solution work for you?