Define a Range based on Cell Values
 

I've searched high and low and can't get this to work.

I have a range of data from A:M.

I want to create ranges within that range for use in charts, but the data ranges will be grouped by the data in column A. For example:

A B C D
banana 10 5.0% 5.6
banana 11 5.1% 7.2
banana 9 4.8% 6.3
apple 7 6% 5
apple 8 18% etc...
orange etc etc etc
orange etc etc etc
orange etc etc etc

I need to name a range1 that would include all the rows that have "banana" in A, and name a range2 that would include all the rows with "apple" in A, and name a range3 that would include all the rows with "orange" in A.

I think there is something I can do with INDEX, but I just can't get it. One note - the number of rows in each range could change month-to-month, so the references can not be static.

Ideas please!!!!!!!

Would the values in A be grouped together always? i.e. Oranges would be in A3, A4 & A5 and never in A3, A5 & A21?

Yes, I can presort so that would be in effect.

Define a Range based on Cell Values
 

Il 05/02/2013 21:06, a.c.sanchez ha scritto:
I've searched high and low and can't get this to work.

I have a range of data from A:M.

I want to create ranges within that range for use in charts, but the
data ranges will be grouped by the data in column A. For example:

A B C D
banana 10 5.0% 5.6
banana 11 5.1% 7.2
banana 9 4.8% 6.3
apple 7 6% 5
apple 8 18% etc...
orange etc etc etc
orange etc etc etc
orange etc etc etc

I need to name a range1 that would include all the rows that have
"banana" in A, and name a range2 that would include all the rows with
"apple" in A, and name a range3 that would include all the rows with
"orange" in A.

I think there is something I can do with INDEX, but I just can't get it.
One note - the number of rows in each range could change
month-to-month, so the references can not be static.

Ideas please!!!!!!!





Try
=OFFSET(rng,MATCH("apple",rng,0)-1,,COUNTIF(rng,"apple"),4)

E.