Dynamic named range

[jcooooper]

Hi all,

If I wanted to create a name ranged of the cells in column A, starting when column B says "Equity", and ending when the work "Fixed Income" starts, how would I go about it? (i.e named range should = A5:A15)

I've tried something like this but no bueno

=OFFSET(MATCH("EQUITIES",B:B,0),0,-1,COUNTIF(B:B,"EQUITIES"),1)

[TABLE="width: 201"]
<tbody>[TR]
[TD]GB92737[/TD]
[TD]Alternatives[/TD]
[/TR]
[TR]
[TD]G73607[/TD]
[TD]Alternatives[/TD]
[/TR]
[TR]
[TD]GB96017[/TD]
[TD]Alternatives[/TD]
[/TR]
[TR]
[TD]LP6121[/TD]
[TD]Alternatives[/TD]
[/TR]
[TR]
[TD]GB908[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]FT_U00PX[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]GB9[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]GB906[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]6D20[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]GB921004[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]GB3001[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]2845001[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]MS_D[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]SG001[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]SNP500PX[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]GB9213[/TD]
[TD]Fixed Income[/TD]
[/TR]
[TR]
[TD]G006[/TD]
[TD]Fixed Income[/TD]
[/TR]
[TR]
[TD]GB92772022[/TD]
[TD]Fixed Income[/TD]
[/TR]
[TR]
[TD]GB92772024[/TD]
[TD]Fixed Income[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=OFFSET(Sheet2!A1,MATCH("EQUITIES",Sheet2!B:B,0)-1,,COUNTIF(Sheet2!B:B,"EQUITIES"),1)
change sheet name to suit

 

[jcooooper]

How about
=OFFSET(Sheet2!A1,MATCH("EQUITIES",Sheet2!B:B,0)-1,,COUNTIF(Sheet2!B:B,"EQUITIES"),1)
change sheet name to suit

Works perfect, thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback