Change/add the defined references, as follows (Insert Name Define):

Name: Array1

Refers to:
=LARGE(IF((Range=NewSheet!$A8)+(ROW(Range)=IF(Firs tRow<NewSheet!$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Click Add

Name: Array2

Refers to:
=LARGE(IF((Range=NewSheet!$A8)+(ROW(Range)=IF(Firs tRow<NewSheet!$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Click Add

Name: FirstRow

Refers to:
=INDEX(Sheet1!$B$19:$D$19,MATCH(NewSheet!$A8,{"50-51","55-56","59-60"},0)
)

Adjust the range B19:D19 accordingly. Also, you can replace the array
constant {...} with a reference to a range of cells. So, for example,
if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
respectively, the MATCH part could be changed to...

MATCH(NewSheet!$A8,Sheet1!$B$17:$D$17,0)

You can use any other cells you want, but don't use row 18 on Sheet1
since that would affect the calculations.

Click Add

Name: NumCount

Refers to:
=COUNTIF(Range,NewSheet!$A8)+(FirstRow<NewSheet!$ A8)

Click Add

Name: Range

Refers to:
=INDEX(Sheet1!$B$18:$D$5000,0,MATCH(NewSheet!$A8,{ "50-51","55-56","59-60"
},0))

With regards to the range and constant array, the same thing applies
here as in the reference for FirstRow.

Click Ok

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi Domenic,

You'll get a #REF! error if there's no data in your range that meets the
criterion or if there's only one cell that meets the criterion, and that
cell is the first one, B19. Is this the case?

No to the above. My mistake as usual.

The reason I'm getting the #REF! error on all rows but the first: I
carelessly omitted part of the sentence in the last paragraph of my original
explanation. In full it should have read:

The Formula placed in the New Sheet and copied across column B to however
many columns there are Rows of TEXT values needs to be flexible enough to
identify TEXT values beyond Row397 and be adaptable to look for other TEXT
values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
(Sample Data extract Sheet1).
So, on the Source Data sheet to find text value 50-51 need to look in column
B, to find text value 55-56 need to look in column C, to find text value 59-
60 need to look in column D etc. Each text value range is in a different
column on the Source Data Sheet.

Expected Results – New Sheet:
ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
etc

Row8 50-51 16 81 30 93 30 4 2
49 64
Row9 55-56
Row10 59-60
etc

The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
referencing is absoluted based on the information in my original Post. Do I
need to create separate Named Formulas for each individual text value range
to have the calculated intervals returned.

If you can offer any further suggestions - most appreciated.

Apologies for confusion.

Thanks
Sam