Hi Domenic,

Your suggested amendments to accommodate my scenario works perfect!

Thank you very much for your brilliant assistance and your time - very much
appreciated.

Cheers
Sam

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

Name: Array1

Refers to:
=LARGE(IF((Range=NewSheet!$A8)+(ROW(Range)=IF(Fir stRow<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(Fir stRow<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!

Hi Domenic,

[quoted text clipped - 39 lines]
Thanks
Sam


--
Message posted via http://www.officekb.com