Dynamic Named Range - Select only part of the data

[Myriad_Rocker]

So I'm trying to implement some dynamic named ranges in a sheet of data that I have so that I can look up values based on those ranges. Basically, it is sales and quantity data that is divided out by Region, Brand, Pack, and Product. I need to select, for example, only the 'Atlantic' region's data.

I have attached an example spreadsheet of my data.

This is the formula that I was trying to use, but it errors and I'm not sure why.
OFFSET(CELL("address",INDEX('Sheet1'!$A:$A,MATCH("Atlantic",'Sheet1'!$A:$A,0),1)),0,0,COUNTIF('Sheet1'!$A:$A,"Atlantic"),6)

Here is the file: http://www.mediafire.com/file/wetylp1c188ez2x/NamedRange.xls

Sure would appreciate some help on this.

Oh, and IF possible, I'd like for the named range to select only from Brand onward (not include the Region column) because I'll be doing a vlookup on brand using the named range.

 

[T. Valko]

So I'm trying to implement some dynamic named ranges in a sheet of data that I have so that I can look up values based on those ranges. Basically, it is sales and quantity data that is divided out by Region, Brand, Pack, and Product. I need to select, for example, only the 'Atlantic' region's data.

I have attached an example spreadsheet of my data.

This is the formula that I was trying to use, but it errors and I'm not sure why.
OFFSET(CELL("address",INDEX('Sheet1'!$A:$A,MATCH("Atlantic",'Sheet1'!$A:$A,0),1)),0,0,COUNTIF('Sheet1'!$A:$A,"Atlantic"),6)

Here is the file: http://www.mediafire.com/file/wetylp1c188ez2x/NamedRange.xls

Sure would appreciate some help on this.

Oh, and IF possible, I'd like for the named range to select only from Brand onward (not include the Region column) because I'll be doing a vlookup on brand using the named range.

For the entire Atlantic range (column A to column F):

OFFSET($A$2,MATCH("atlantic",$A$2:$A$65536,0)-1,,COUNTIF($A:$A,"atlantic"),6)

For the Atlantic range from column B to column F:

OFFSET($A$2,MATCH("atlantic",$A$2:$A$65536,0)-1,1,COUNTIF($A:$A,"atlantic"),6)

 

[Aladin Akyurek]

So I'm trying to implement some dynamic named ranges in a sheet of data that I have so that I can look up values based on those ranges. Basically, it is sales and quantity data that is divided out by Region, Brand, Pack, and Product. I need to select, for example, only the 'Atlantic' region's data.

I have attached an example spreadsheet of my data.

This is the formula that I was trying to use, but it errors and I'm not sure why.
OFFSET(CELL("address",INDEX('Sheet1'!$A:$A,MATCH("Atlantic",'Sheet1'!$A:$A,0),1)),0,0,COUNTIF('Sheet1'!$A:$A,"Atlantic"),6)

Here is the file: http://www.mediafire.com/file/wetylp1c188ez2x/NamedRange.xls

Sure would appreciate some help on this.

Oh, and IF possible, I'd like for the named range to select only from Brand onward (not include the Region column) because I'll be doing a vlookup on brand using the named range.

The following defines Atlantic...

=OFFSET(Sheet1!$B$2,MATCH("Atlantic",Sheet1!$A:$A,0)-MIN(ROW(Sheet1!$A$2)),0,COUNTIF(Sheet1!$A:$A,"Atlantic"),5)

The set up you want requires that the Region data (column A) is always in ascending order.

Follow the same pattern for other regions.

 

[T. Valko]

For the Atlantic range from column B to column F:

OFFSET($A$2,MATCH("atlantic",$A$2:$A$65536,0)-1,1,COUNTIF($A:$A,"atlantic"),6)

Slight tweak...

Since we shifted the range over one column to the right we then need to reduce the range width by one column so...

For the Atlantic range from column B to column F:

OFFSET($A$2,MATCH("atlantic",$A$2:$A$65536,0)-1,1,COUNTIF($A:$A,"atlantic"),5)

 

[Myriad_Rocker]

Awesome. Thanks guys!