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]

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.

Can't say I agree with that - VBA UserForms in Excel are a huge pain in the neck, IMO!

All things equal, if you have Microsoft Access (which is designed for database work), forms are MUCH easier to use in Access, as you just drag and drop the fields in your form (and don't need to use VBA to map each and every field manually!). Of course, it helps to be knowledgeable and proficient is Access.

Power Query could be a possible Excel solution too.

 

[Fluff]

Thanks very much for the solution.

Glad we could help & thanks for the feedback.

 

[ArranI]

Did fluff's solution work for you?

Hi Joe,

Yes, Fluff's solution works well.

I've marked it as the solution, is there anything else I need to do to close the thread?

 

[Fluff]

is there anything else I need to do to close the thread?

Nope, you've said it works & marked it as the solution, so that's fine.