Dynamic Range Names

Looking to create three dynamic ranges that move based on field 1 of the attached spreadsheet sample.

The first range would be named "Blue", the second "Red" and the third "Green". Each day, new data will populate this same sheet (in the same format), only there will be a different number of rows for each color (sometimes less, sometimes more). What is the formula to create these dynamic ranges? If you can tell me how to do one of them, I can likely figure out the other two.

Thanks greatly, in advanced. This will really help me.

D

But will the Data be always sorted in field 1? Like will all the Blue entries always be together?

Yes, that's correct.

What are you trying to do with this data?

(your profile says 2003, but your upload suggests 2007 or later. Please revise your profile)

I work in both. The best solution will work in 2003 and 2010. There are other formulas that refer to the range names mentioned below. Right now, we re-name the ranges every day but would like this happen automatically. Does this make sense?

One way...

Blue =

=OFFSET(A2,MATCH("blue",A2:A1000,0)-1,0,COUNTIF(A:A,"blue"),5)

Use a reasonable end of range in column A where I use down to A1000.

Here's a named range refers to, for Blue:

Formula:

Please Login or Register  to view this content.

just change the named range name , and the "color" for the other ranges

Hope this helps

EDIT-
And as Tony suggested in thread above Change the $A:$A references to a reasonable size, I used the whole column because I didn't know your acceptable range, but maybe:

Formula:

Please Login or Register  to view this content.