Offset formaula or better way?

[SeanO123]

Hi All,

Hopefully a simple issue...

I am trying to pick every a cell every 6 places from the reference cell and cant seem to get the OFFSET formula to do what I want.

Ther is most likely an easier way to do this but the base data is multiple rows per unique reference so makes this not possible to do the drag method.

The below is the current fixed cell link in place which i want to automate so it is not do cell by cell. Each reference below is actually A18:A23 but edited to A18 so not sure if this is causing the problem as it is a range rather than a single cell?

Current results using offset just gives same results as drag method so doesnt consolidate for each 6th cell.

Thanks in advance for any help you can provide

 

[bebo021999]

To get value from column A, try:

=INDEX('Procurement Schedule'!$A:$A,(ROWS($1:1)-1)*6+18)

with 6 = step and 18 is first row

 

[Peter_SSs]

Given your Excel version, there are other possibilities that do not require you to copy anything down the columns like that.
(I have done smaller versions just to show the idea)

First, here is a small sample of my 'Procurement Schedule' sheet

23 03 02.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
18	710	64	898	155	481	754	592	257	817	597	316	659	61	956
19	690	381	964	553	994	814	906	365	865	40	751	50	963	625
20	390	545	391	439	865	747	978	323	785	322	258	424	307	874
21	560	674	382	645	733	658	370	543	246	144	609	779	659	59
22	915	772	694	543	246	760	330	905	813	924	847	252	330	812
23	91	299	3	253	713	887	228	159	337	132	680	706	105	831
24	247	473	7	67	988	320	411	813	808	448	184	604	416	417
25	388	55	2	520	727	614	922	615	710	697	354	662	268	945
26	931	520	969	419	431	773	310	85	24	580	288	280	953	98
27	230	885	456	825	481	355	205	989	67	80	300	450	188	971
28	965	407	657	53	546	422	275	409	197	137	129	10	407	171
29	288	406	874	335	793	781	137	563	555	126	952	558	969	973
30	786	144	145	881	752	40	221	606	2	164	909	32	227	266
31	872	627	424	329	644	898	354	75	651	552	749	975	667	409
32	550	636	986	39	396	440	387	680	729	827	827	676	830	234
33	256	903	754	510	483	47	83	377	233	168	343	223	719	680
34	165	773	737	975	840	846	327	276	16	417	596	742	528	462
35	666	93	178	772	399	617	275	390	682	778	981	52	824	244
36	604	895	31	838	983	511	535	458	851	783	956	256	352	734
37	690	711	959	765	524	241	487	602	27	339	293	814	610	552
Procurement Schedule

To get every 6 rows from a single column you can use a formula like I have in A2 below. This will give you 10 results from every 6th row with that single formula in A2 only.

Also IF the 3 results columns are adjacent (Cannot tell for sure from your image but it looks like they may not be adjacent) then you could in fact get all 3 with a single formula in a single cell like I have in C2 which produces 10 lots of every 6 rows from columns A, B & N of the above sheet.

23 03 02.xlsm
	A	B	C	D	E
1
2	710		710	64	956
3	247		247	473	417
4	786		786	144	266
5	604		604	895	734
6	490		490	79	108
7	598		598	428	732
8	428		428	985	408
9	406		406	598	25
10	61		61	682	850
11	830		830	119	705
12
Every 6

Cell Formulas
Range	Formula
A2:A11	A2	=INDEX('Procurement Schedule'!A18:A1000,SEQUENCE(10,,,6))
C2:E11	C2	=INDEX('Procurement Schedule'!A18:N1000,SEQUENCE(10,,,6),{1,2,14})
Dynamic array formulas.