Count Rows in a Dynamic Range

[joseulloa22]

Hello- good morning
Looking for help. The below table can start in any row (i.e, cell A17= "Components" could be in A150). Columns will remain as it shows . In column I (starting in I22 in this case), I would like to have the content of B22. On this case, I used the following formula but since my # of rows will change I think that rather than using A21 I would need a dynamic reference but not sure how to do that

=OFFSET(INDEX(A:A,MATCH("Components",A:A,0)),ROWS(INDEX(A:A,MATCH("Components",A:A,0)):A21),1)

 

[C Moore]

Is it always 5 rows between components and the item? (i.e. row 17 + 5 = row 22) If so +5 could replace :A21

 

[joseulloa22]

Thanks. Yes, it is always 5 rows between Components and the first item . I added +5 in place of :A21 but it worked well for the first row (I22 in this case) but then when dragging down the formula it is pulling the same result. I guess in I22 it should add 5 as you said, then in I23 add 6, i24 add 7, etc. but not sure how to do this dynamically

 

[C Moore]

You want col I to equal col B for all entries?

 

[joseulloa22]

Yes, please. That's the output I am looking for.

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
17	Component
18		Short										Data1	a	b	c	d	e
19												Data1	a	b	c	d	e
20		Identification										Data1	a	b	c	d	e
21		---------------										Data1	a	b	c	d	e
22		Data1	a	b	c	d	e	f	Data1			Data1	a	b	c	d	e
23		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
24		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
25		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
26		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
27		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
28		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
29		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
30		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
31		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
32		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
33		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
34		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
35		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
36		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
37		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
38		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
39		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
40		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
41		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
42		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
43		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
44		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
45		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
46		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
47		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
48		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
49		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
50		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
51		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
52		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
53		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
54		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
55		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
56		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
57		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
58		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
59		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
60		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
61		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
62		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
63		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
64		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
65		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
66		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
67		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
68		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
69		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
70		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
71		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
72		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
73		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
74		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
75		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
76		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
77		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
78		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
79		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
80		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
81		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
82		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
83		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
84		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
85		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
86		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
87		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
88		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
89		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
90		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
91		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
92		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
93		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
94		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
95		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
96		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
97		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
98		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
99		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
100		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
101		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
102		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
103		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
104		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
105		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
106		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
107		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
108		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
109		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
110		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
111		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
112		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
113		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
114		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
115		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
116		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
117		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
118		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
119		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
120		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
121		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
122		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
123		Data1	a	b	c	d	e	f				Data1	a	b	c	d	e
124		Data1	a	b	c	d	e	f
125		Data1	a	b	c	d	e	f
126		Data1	a	b	c	d	e	f
127		Data1	a	b	c	d	e	f
128		Data1	a	b	c	d	e	f
129
130
131
Sheet3

Cell Formulas
Range	Formula
L18:Q123	L18	=OFFSET(INDEX(A:A,MATCH("Component",A:A,0)),5,1,MATCH(REPT("Z",255),B:B)-MATCH(REPT("Z",255),A:A)-5,6)
Dynamic array formulas.

#VALUE!

 

[joseulloa22]

Thanks CA_Punit. I am getting an error when entering in L18 the suggested formula

 

[CA_Punit]

Enter With Control+Shift+Enter and Notr Just Enter and Drag the Formula Down

 

[joseulloa22]

Thanks CA_Punit. Did it as indicated. I see I am getting the same result as I drag down the formula. Please, remember column I should equals column B for all entries. In my example above, I22 should equals B22, I23 should equals B23, I24=B24, etc.

 

[CA_Punit]

Can you post a sample using Xl2BB Addin available in the Right Hand Side of the reply Box

Download and run the file
If not able to do it then please post your data through Drop Box