Dynamic Range and Index Match

[carrollm]

I have written these questions on the attached spreadsheet

Question 1 How do I write the correct formula for the above row 4
Question 2 How can I make the named ranges dependent on the values in column A
so if I load new data into columns B and C (the new data may extend down additional rows)
With the new data - RANGE1 would expand addl rows if "BRONTON" was in more rows that is
curtenly shown - and so-on for the other ranges.

Thanks Carroll

[azumi]

Maybe, use this formula on E4 and copied cross:
=IFERROR(INDEX(dynamic,AGGREGATE(15,6,(ROW(dynamic)-MIN(ROW(dynamic))+1)/(dynamic=E$2),COUNTIF($E$2:E2,E2))),"")

Inside "dynamic" named ranges using this formula:
=OFFSET($C$4,0,0,COUNTA($C:$C),1)

[carrollm]

Auzmi thanks for your help
question - when I used the iferror formula in cell E4 it returned blank. When I copied across - all blank

question - not sure what you mean Inside "dynamic" named ranges using this formula:
I know how to edit a named range but not sure where to put the formula

thanks Carroll

[JeteMc]

Look at the file attached to azumi's post. The OFFSET based formula is in the 'Refers To:' window of the named range dialog box. I believe that putting the formula there will resolve the first issue also, although I don't know the expected values in columns E:J.
Let us know if you have any questions.