Dynamic Named Range: Simulating a filtered region
 

Hi!

Let's make it simple: 4 Columns of data: ID, NAME, DESCRIPTION,
STATUS.


STATUS can take the following values: Incomplete, Under Review,
Approved, Rejected, Obsolete.


I am creating a Dynamic Named Range on my ID + NAME columns (It serves
as the Data for a ListBox Control somewhere else in my workbook). Let's

call this Range: ITEMLIST.


Although this is all fine, I am still looking for a way to optimize the

"RefersTo" formula of Range ITEMLIST to "exclude" or "filter" any rows
for which column "Status" is set to Rejected AND Obsolete. I tried a
lot of things using MATCH, ADDRESS, INDIRECT, etc.. but I can't find
the way to success.


For Example, if I have the following data:


Row 01: ITEM.001 Name001 Description001 Incomplete
Row 02: ITEM.002 Name002 Description002 Approved
Row 03: ITEM.003 Name003 Description003 Obsolete
Row 04: ITEM.004 Name004 Description004 Approved
Row 05: ITEM.005 Name005 Description005 Incomplete
Row 06: ITEM.006 Name006 Description006 Rejected
Row 07: ITEM.007 Name007 Description007 Approved


I would expect my Dyanamic Range "ITEMLIST" to include Row 01 / 02 / 04

/ 05 / 07.


If I could create a Temporary Named Range "STATUS" and apply some kind
of filter on it (Without actually filtering the Excel Sheet with the
Data Filters Functionalities), Then I could probably change my
Reference on Range ITEMLIST to something like:


=OFFSET(STATUS,0,-3,COUNTA(STATUS),1)


Which would refer to the Filtered STATUS Range.


Anyone has a clue on how this could be possible? In the end all I want,

is my ITEMLIST to not show the Obsolete and Rejected Entries for future

usage and selection.


Thanks all!