dynamic named range using offset/counta but with a twist

Question

im creating a auto-updating pivot table from a dynamic named range that is only 1 column. the range in question has data below it and above it. is it possible to still have a dynamic named range using the offset formula, but the starting/stopping point is a trigger word/text? for example

don't look here
don't look here
don't look here
don't look here
trigger text (not included in named range)
data
data
data
(insert more data here)
data
data
(insert more data here)
data
trigger text (not included in named range)
don't look here
don't look here
don't look here

the dynamic named range I want is where there is "data". the start point and endpoints are both dynamic. how do you fix this formula to accommodate this need?

=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

the starting point in my example is $A89, but tomorrow it could be $A91. similar scenario for the endpoint

something like =offset(sheet2!(1 cell below trigger word 1),0,0,counta(sheet2!$A:(1 cell above trigger word 2)x,x)

the trigger texts will always be the same and never change, but they are two different texts. if that makes sense. one is "XYZ" the other is "ZYX" and they will never change from that — dusty_j

Scott Craner Scott Craner · Accepted Answer · 2019-02-06T14:35:37

If your trigger text can be different, 1 to begin and a different to end then:

 =INDEX(Sheet2!$A:$A,MATCH("BeginTrigger",Sheet2!$A:$A,0)+1):INDEX(Sheet2!$A:$A,MATCH("EndTrigger",Sheet2!$A:$A,0)-1)

dynamic named range using offset/counta but with a twist

2 Answers