trying to eliminate cut/paste for this scenario

the obvious response would be filter > copy/paste, but i am trying my hardest to keep my spreadsheet 'automated'.

here's the deal: i have a sheet (also a named array) that lists a bunch of partner codes. each partner code has a 'type' associated with it.

column A / column B
AZSU / ALCP
ATLA / SLCP
BOCO / ALCP
ICPA / SLCP

here's what i need to happen:

sheet1 (named ALCP)
AZSU ... the rest of the sheet is vlookup/if statement generated
BOCO ... the rest of the sheet is vlookup/if statement generated

sheet2 (named SLCP)
ATLA ... the rest of the sheet is vlookup/if statement generated
ICPA ... the rest of the sheet is vlookup/if statement generated

again, i need this generated via formula.
seems simple enough???

thanks,
mike

If you can afford to add a dummy row in there (on the page that you filter, and again on the other page) you could do the following

On the Array Sheet add the following forumula in a column (I'm using Column C)
=Countif($B$1:$B1,$B1) and copy down for the list.

On Sheet1 (ALCP) put in the following formula in Cell A1
=SUMPRODUCT(--(Array!$B$1:$B$100="ALCP"),--(Array!$C$1:$C$100=ROW()),ROW(Array!$C$1:$C$100))

That will return the row that the value AZSU is on. In Cell B1 put the following formula
=IF(A1=0,"",INDIRECT("Array!A"&A1))

Copy that formula down as much as needed, if you copy down to 30 rows and there are only 15 items, the last 15 will just show a 0 in the A and nothing in B.

There is probably an easier way of doing it, but this works.

Hope it helps..

John