Filtering problem - long question

[Steve]

ALMOST USELESS BACKGROUND INFORMATION



I belong to a large organisation. Recently, my services were lent to another
department to solve a roster attendance reporting problem. Tools used to
solve the problem included use of existing mainframe program (PeopleSoft),
email and Excel. The Excel workbook used Excel filters to allow reporting by
Department, Section and team using a three-digit code available in
PeopleSoft.



PROBLEM BACKGROUND

I use a form to apply a filter to a range of data. The filter works on a
three-digit code. The code was set up (by me) to allow easy filtering by
Department, Section and Team. I used a form and the following <snipped code>
to apply filters:

Private Sub UserForm_Initialize()

Set rng = ThisWorkbook.Names("teamarray").RefersToRange

rArray = rng.Value

With Me.ComboBox1

.List() = rArray

.ListIndex = False ' no selected item

End With

End Sub



This setup enables column A of teamarray range to be used as the
description the user sees in the combo and column B is the (invisible to
user) filter criteria.



In the above, teamarray is a named range that allows power-users to set
filter criteria. It was configured to allow matrix selection: EXAMPLES:



- if a section exists in more than one department {it did} users can set a
criteria in the team array
Column A Column B

anynamewantedincombo ?A?



Similarly, if they wanted a specific team they could set up in the array

Team 1 *1



MY DILEMMA

People in the department decided to restructure and add a sub-section. As
they are restricted to a three-digit code (at mainframe level), without
consulting me about possible ramifications, they decided:

First character - department

Second character = section

Third character: 0 = sub-section A; 1-4 = sub section B; 5-9 = sub-section
C.



They don't want to pay for my services again. I am unwilling to re-write the
project in my time to add an advanced filter. Is there something simple I am
missing that can be done by power users in the teamarray range that would
give a result? For example is there something that would allow me to do
something like:

Sub Section A 0

SubSection B ?? [between 1 and 4]

SubSection C ?? [>4]

 

[Tom Ogilvy]

You can use a formula as the criteria - and you could make that fairly
complex - but the criteria range with a formula is slightly different than
the criteria range for the type of criteria you are using. for a formula,
the header in the criteria has to be a dummy value.

So I would say there is nothing that meets your specific description.

--
Regards,
Tom Ogilvy

ALMOST USELESS BACKGROUND INFORMATION



I belong to a large organisation. Recently, my services were lent to another
department to solve a roster attendance reporting problem. Tools used to
solve the problem included use of existing mainframe program (PeopleSoft),
email and Excel. The Excel workbook used Excel filters to allow reporting by
Department, Section and team using a three-digit code available in
PeopleSoft.



PROBLEM BACKGROUND

I use a form to apply a filter to a range of data. The filter works on a
three-digit code. The code was set up (by me) to allow easy filtering by
Department, Section and Team. I used a form and the following <snipped code>
to apply filters:

Private Sub UserForm_Initialize()

Set rng = ThisWorkbook.Names("teamarray").RefersToRange

rArray = rng.Value

With Me.ComboBox1

.List() = rArray

.ListIndex = False ' no selected item

End With

End Sub



This setup enables column A of teamarray range to be used as the
description the user sees in the combo and column B is the (invisible to
user) filter criteria.



In the above, teamarray is a named range that allows power-users to set
filter criteria. It was configured to allow matrix selection: EXAMPLES:



- if a section exists in more than one department {it did} users can set a
criteria in the team array
Column A Column B

anynamewantedincombo ?A?



Similarly, if they wanted a specific team they could set up in the array

Team 1 *1



MY DILEMMA

People in the department decided to restructure and add a sub-section. As
they are restricted to a three-digit code (at mainframe level), without
consulting me about possible ramifications, they decided:

First character - department

Second character = section

Third character: 0 = sub-section A; 1-4 = sub section B; 5-9 = sub-section
C.



They don't want to pay for my services again. I am unwilling to re-write the
project in my time to add an advanced filter. Is there something simple I am
missing that can be done by power users in the teamarray range that would
give a result? For example is there something that would allow me to do
something like:

Sub Section A 0

SubSection B ?? [between 1 and 4]

SubSection C ?? [>4]

 

[Steve]

Tom
Thank you. I'll do a little more work on formula (which has so far been
unproductive) before telling them to go back and figure out a new
three-digit code system.

Steve

You can use a formula as the criteria - and you could make that fairly
complex - but the criteria range with a formula is slightly different than
the criteria range for the type of criteria you are using. for a
formula,
the header in the criteria has to be a dummy value.

So I would say there is nothing that meets your specific description.

--
Regards,
Tom Ogilvy

ALMOST USELESS BACKGROUND INFORMATION



I belong to a large organisation. Recently, my services were lent to
another
department to solve a roster attendance reporting problem. Tools used to
solve the problem included use of existing mainframe program
(PeopleSoft),
email and Excel. The Excel workbook used Excel filters to allow reporting
by
Department, Section and team using a three-digit code available in
PeopleSoft.



PROBLEM BACKGROUND

I use a form to apply a filter to a range of data. The filter works on a
three-digit code. The code was set up (by me) to allow easy filtering by
Department, Section and Team. I used a form and the following <snipped
code>
to apply filters:

Private Sub UserForm_Initialize()

Set rng = ThisWorkbook.Names("teamarray").RefersToRange

rArray = rng.Value

With Me.ComboBox1

.List() = rArray

.ListIndex = False ' no selected item

End With

End Sub



This setup enables column A of teamarray range to be used as the
description the user sees in the combo and column B is the (invisible to
user) filter criteria.



In the above, teamarray is a named range that allows power-users to set
filter criteria. It was configured to allow matrix selection: EXAMPLES:



- if a section exists in more than one department {it did} users can set
a
criteria in the team array
Column A Column B

anynamewantedincombo ?A?



Similarly, if they wanted a specific team they could set up in the array

Team 1 *1



MY DILEMMA

People in the department decided to restructure and add a sub-section. As
they are restricted to a three-digit code (at mainframe level), without
consulting me about possible ramifications, they decided:

First character - department

Second character = section

Third character: 0 = sub-section A; 1-4 = sub section B; 5-9 =
sub-section
C.



They don't want to pay for my services again. I am unwilling to re-write
the
project in my time to add an advanced filter. Is there something simple I
am
missing that can be done by power users in the teamarray range that would
give a result? For example is there something that would allow me to do
something like:

Sub Section A 0

SubSection B ?? [between 1 and 4]

SubSection C ?? [>4]