Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering problem - long question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering problem - long question
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 "Steve" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering problem - long question
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 "Tom Ogilvy" wrote in message ... 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 "Steve" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
Filtering question | Excel Discussion (Misc queries) | |||
Filtering question | Excel Discussion (Misc queries) | |||
Filtering question | Excel Discussion (Misc queries) | |||
Filtering long text | Excel Worksheet Functions |