"Max" wrote...
One way to set-it up ..

In sheet named: Master
-------------------
Assume the sample table below is in cols A to G
data from row2 down
(with the key column "Type" in col B), viz:

Region___Type
midwest Direct 12000 500 0 500 12500
....
southwest Dealer 3424 200 2 45664 67898
etc

Don't need headers in these columns.

List across in say, I1:K1
the 3 "Type"s: Direct, National, Dealer

Put in I2: =IF($B2="","",IF($B2=I$1,ROW(),""))

Alternatively,

I2:
=IF($B2=I$1,COUNTIF(I$1:I1,"0")+1,"")

Then I2 filled right into J2:K2, then I2:K2 filled down as far as needed.
Name the entire range spanned by columns A through K from row 1 though the
bottommost record in columns A through G something like AMT.

The $B2="" test is pointless, btw.

In a new sheet named: Direct
---------------------------------------
Let's reserve cell A1 for the "Type" name

Put in A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname: Direct into A1.
But you need to save the file first)
....

Alternatively, define the name WSN referring to

=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("'Master'!"&CHOO SE(MATCH($A$1,
Master!$I$1:$K$1,0),"I:I","J:J","K:K")),ROW(A1) ),
INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$ 1:$K$1,0),
"I:I","J:J","K:K")),0)),"",OFFSET(Master!$A$1,
MATCH(SMALL(INDIRECT("'Master'!"&CHOOSE(MATCH($A$ 1,
Master!$I$1:$K$1,0),"I:I","J:J","K:K")),ROW(A1) ),
INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$ 1:$K$1,0),
"I:I","J:J","K:K")),0)-1,COLUMN(A1)-1))
....

INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$1 :$K$1,0),
"I:I","J:J","K:K"))

?!

OFFSET(Master!$H:$H,0,MATCH($A$1,Master!$I$1:$K$1, 0))

is shorter and more efficient.

But continuing with my alternative setup,

A2:
=IF(COUNTIF(INDEX(AMT,0,MATCH(WSN,INDEX(AMT,1,0),0 )),"0")=ROW()-1,
LOOKUP(2,1/(INDEX(AMT,0,MATCH(WSN,INDEX(AMT,1,0),0))=ROW()-1),
INDEX(AMT,0,COLUMN())),"")

Fill A2 right into B2:G2, then fill A2:G2 down until it evaluates to "".

Now, just duplicate / make a copy of the sheet: Direct,
rename it as: National
and you'll get the "filtered" rows for National:
....