Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Advanced Filter with a loop?
Hi -
This one's got me in knots ... hoping someone can help ... :) On Sheet1 9 (named 'tracker'), columns B-H contain information related to employee performance (namely, missed time-clock punches). Column B holds the date of the infraction. Users interact with Sheet1 via one two userforms -- 'DateEntry' and 'Report'. The 'Report' userform contains a listbox ('empData') that initializes with all data from the 'Tracker' worksheet (using a dynamic named range = 'nrData'). There're also 2 textboxes ("tframe" and "MPno") that will hold user entered parameters ... now we get to the real question. I need a procedure that will take the values of 'tframe' and 'MPno' and apply them to nrData, returning a list of employees that meet the 2 criteria. For example: tframe = 30 MPno = 3 The procedure would loop through nrData and return a list of employees (and their data from nrData) that had 3 or more missed time-clock punches in the last 30 days. I have absolutely no idea where to start with this ... I *think* an Advanced-Filter may be needed, but beyond that, I'm lost .... Help! TIA, Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Advanced Filter with a loop?
Ray,
You could use a pivot table to do all that your describe - show all your desired values in one table, allow the user to choose the specific set of data being viewed at any one time, etc. HTH, Bernie MS Excel MVP "Ray" wrote in message ... Hi - This one's got me in knots ... hoping someone can help ... :) On Sheet1 9 (named 'tracker'), columns B-H contain information related to employee performance (namely, missed time-clock punches). Column B holds the date of the infraction. Users interact with Sheet1 via one two userforms -- 'DateEntry' and 'Report'. The 'Report' userform contains a listbox ('empData') that initializes with all data from the 'Tracker' worksheet (using a dynamic named range = 'nrData'). There're also 2 textboxes ("tframe" and "MPno") that will hold user entered parameters ... now we get to the real question. I need a procedure that will take the values of 'tframe' and 'MPno' and apply them to nrData, returning a list of employees that meet the 2 criteria. For example: tframe = 30 MPno = 3 The procedure would loop through nrData and return a list of employees (and their data from nrData) that had 3 or more missed time-clock punches in the last 30 days. I have absolutely no idea where to start with this ... I *think* an Advanced-Filter may be needed, but beyond that, I'm lost .... Help! TIA, Ray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Advanced Filter with a loop?
Hi Bernie -
Thanks for responding ... you're correct, it can certainly be done that way. My 'hesitation' is that many (possibly most) of the users aren't really that comfortable with Excel, so I'm attempting to make it as un-Excel like as possible. All user-interaction will be through userforms ... Wait, could I have the pivot-table be created/updated 'behind the scenes' (on a hidden worksheet) and then show the results in the listbox? I assume I'd need a named range for the pivot-table -- how is that done? this is interesting ... I'd have numerous applications for this! thanks again, ray |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Advanced Filter with a loop?
You can change what a pivot table shows easily in code, but just make sure that you set all the
items visible prior to hiding other items, since at least one item need to be visible or your code will error out. For example, witht he value that you want to show in Worksheets("Sheet1").Range("A1") Sub Macro2() Dim myItem As PivotItem For Each myItem In Worksheets("Sheet3").PivotTables("PivotTable2").Pi votFields("name").PivotItems myItem.Visible = True Next myItem For Each myItem In Worksheets("Sheet3").PivotTables("PivotTable2").Pi votFields("name").PivotItems If myItem.Name < Worksheets("Sheet1").Range("A1").Value Then myItem.Visible = False End If Next myItem End Sub You can, of course, use appropriate objects for the worksheets, pivot tables, etc. You can update the pivot table, and then use GETPIVOTDATA formulas to extract the desired values See this page for how to form the formulas: http://www.contextures.com/xlPivot06.html HTH, Bernie MS Excel MVP "Ray" wrote in message ... Hi Bernie - Thanks for responding ... you're correct, it can certainly be done that way. My 'hesitation' is that many (possibly most) of the users aren't really that comfortable with Excel, so I'm attempting to make it as un-Excel like as possible. All user-interaction will be through userforms ... Wait, could I have the pivot-table be created/updated 'behind the scenes' (on a hidden worksheet) and then show the results in the listbox? I assume I'd need a named range for the pivot-table -- how is that done? this is interesting ... I'd have numerous applications for this! thanks again, ray |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Advanced Filter with a loop?
The other thing that I should mention is that you can quite often just use a SUMPRODUCT formula to
return the value of interest, instead of the pivot table. It is a little easier to setup, if you are just looking for a few values. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... You can change what a pivot table shows easily in code, but just make sure that you set all the items visible prior to hiding other items, since at least one item need to be visible or your code will error out. For example, witht he value that you want to show in Worksheets("Sheet1").Range("A1") Sub Macro2() Dim myItem As PivotItem For Each myItem In Worksheets("Sheet3").PivotTables("PivotTable2").Pi votFields("name").PivotItems myItem.Visible = True Next myItem For Each myItem In Worksheets("Sheet3").PivotTables("PivotTable2").Pi votFields("name").PivotItems If myItem.Name < Worksheets("Sheet1").Range("A1").Value Then myItem.Visible = False End If Next myItem End Sub You can, of course, use appropriate objects for the worksheets, pivot tables, etc. You can update the pivot table, and then use GETPIVOTDATA formulas to extract the desired values See this page for how to form the formulas: http://www.contextures.com/xlPivot06.html HTH, Bernie MS Excel MVP "Ray" wrote in message ... Hi Bernie - Thanks for responding ... you're correct, it can certainly be done that way. My 'hesitation' is that many (possibly most) of the users aren't really that comfortable with Excel, so I'm attempting to make it as un-Excel like as possible. All user-interaction will be through userforms ... Wait, could I have the pivot-table be created/updated 'behind the scenes' (on a hidden worksheet) and then show the results in the listbox? I assume I'd need a named range for the pivot-table -- how is that done? this is interesting ... I'd have numerous applications for this! thanks again, ray |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Advanced Filter with a loop?
Thanks Bernie, I'll check it out ....I love Debra's site ;)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Filter Code / Advanced Filter | Excel Programming | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |