Combine Advanced Filter with a loop?

[Ray]

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

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

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").PivotFields("name").PivotItems
myItem.Visible = True
Next myItem

For Each myItem In Worksheets("Sheet3").PivotTables("PivotTable2").PivotFields("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

 

[Bernie Deitrick]

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

 

[Ray]

Thanks Bernie, I'll check it out ....I love Debra's site