Define a Range based on Cell Values

[a.c.sanchez]

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 th
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 hav
"banana" in A, and name a range2 that would include all the rows wit
"apple" in A, and name a range3 that would include all the rows wit
"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 chang
month-to-month, so the references can not be static.

Ideas please!!!!!!

 

[Spencer101]

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 th
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 hav
"banana" in A, and name a range2 that would include all the rows wit
"apple" in A, and name a range3 that would include all the rows wit
"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 chang
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 b
in A3, A4 & A5 and never in A3, A5 & A21

 

[plinius]

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.