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 followin 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 somehwere 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!

Robidoux Wrote:

>Hi!

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

>STATUS can take the followin 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 somehwere 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!

In order for your listbox to get the correct information, you need to
perform an Advanced Filter of the data to another worksheet with in your
workbook, define the range name for that and then open your list box.
This will allow you to have only the item in your listbox that you want.
But it does require and little VBA or you can do it all manually if you
wish.

If you need code examples, please write me back. me@Joepottschmidt.com

Joe P.

Re: Dynamic Named Range: Simulating a filtered region>But it does require and little VBA or you can do it all manually if you wish.

It could be done with formulas but based on the content of the post I doubt they would want to go that route.

Biff
"Joseph R. Pottschmidt" <me@joepottschmidt.com> wrote in message news:00ce01c682d1$e63952d0$640fa8c0@D119K061...
Robidoux Wrote:

>Hi!

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

>STATUS can take the followin 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 somehwere 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!

In order for your listbox to get the correct information, you need to perform an Advanced Filter of the data to another worksheet with in your workbook, define the range name for that and then open your list box. This will allow you to have only the item in your listbox that you want. But it does require and little VBA or you can do it all manually if you wish.

If you need code examples, please write me back. me@Joepottschmidt.com

Joe P.