View Single Post
  #1  
Old May 17th, 2009, 10:08 AM posted to microsoft.public.excel.misc
WhatsUp31415
external usenet poster
 
Posts: 27
Default How to reference variable range?

I have one column of data (B9:B721) and a parallel column of tags (C9:C721).
The data are arranged with all of "Tag1" data, followed by all of "Tag2"
data, etc.

I want to be able to reference all of the data for "Tag1", or all of the
data for "Tag2", etc. For example:

=FREQUENCY(tagrange,H1:H15)

Currently, I use INDIRECT to construct tagrange:

INDIRECT("B" & MIN(IF(C9:C721="TAG2",ROW(C9:C721))) &
":B" & MAX(IF(C9:C721="TAG2",ROW(C9:C721))))

Is there a better way?

The ideal variable reference would not require an array formula, as the
MIN/IF usage above does, even though the example, FREQUENCY, must be an
array formula anyway.