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)

 

[pjmorris]

How do you place your formula? If it is always placed in Column I and at the same row as the first Identification, for the example above it would be simply =B22 and copy down, but that seems too trivial.

If the copy of the id might be on some other row in Column I then if the first cell you have the formula in is in Row 2 you could use (change the Row()+2 element so the 2 reflects the row the formula is in):

=OFFSET($A$1, MATCH("Components",A:A,0)+ROW()+2,1)

An alternative formula is shown below, and this works wherever you move the data too:

Book1
	A	B	C	D	E	F	G	H	I
1
2									|__A
3									|__B
4									|__C
5									|__D
6									|__E
7									|__F
8
9
10	Components
11		Short
12
13		Identification	Whs	Required	Total req'd	Avail	Short	Errors
14		**********	***	*******
15		|__A	1	6/22/2020	135		135	no	|__A
16		|__B							|__B
17		|__C							|__C
18		|__D							|__D
19		|__E							|__E
20		|__F							|__F
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=OFFSET($A$1, MATCH("Components",A:A,0)+ROW()+2,1)
I15:I20	I15	=OFFSET($A$1, MATCH("Components",A:A,0)+ROW()-(1+MATCH("Components",A:A,0)),1)

HTH

 

[joseulloa22]

Peter, the second formula =OFFSET($A$1, MATCH("Components",A:A,0)+ROW()-(1+MATCH("Components",A:A,0)),1), worked real nice in any row I throw my data between col A and H. It is pulling the content from col B and placing into respective cells in col I. That's phenomenal, thanks so much!
Thanks C Moore and C_Punit as well

 

[pjmorris]

Delighted to have helped and many thanks for the feedback.