How to make Bullen's FilterCriteria() data refresh real-time?

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria() Function.

That said, apparently the only way to refresh the data in the display cell 
is to re-activate the formula by pressing <ENTER> in the Fx Box.

Can the display cells be made to refresh in real-time or, second best, by F9?

FYI (Background Info):

************************************************************II placed this 
formula into cell A1:  =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
*************************************************************
Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen via j-walk.com
    Dim Filter As String
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " AND " & .Criteria2
                Case xlOr
                    Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = Filter
End Function
*************************************************************

Also, why does this formula not refresh as do other formulas in the 
worksheet?  Is it because this particlar Function must be "force-called" for 
each refresh?

TIA Dennis
0
Dennis (299)
3/12/2005 4:17:05 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
279 Views

Similar Articles

[PageSpeed] 42

You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen via j-walk.com
    Application.Volatile
    Dim Filter As String

But the bad thing is that either way, the formula won't recalculate until excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:
> 
> Using XL 2003 & 2000
> 
> OK, with help, I was able to utilize Steve Bullen's FilterCriteria() Function.
> 
> That said, apparently the only way to refresh the data in the display cell
> is to re-activate the formula by pressing <ENTER> in the Fx Box.
> 
> Can the display cells be made to refresh in real-time or, second best, by F9?
> 
> FYI (Background Info):
> 
> ************************************************************II placed this
> formula into cell A1:  =FilterCriteria(A3)
> Cell A3 contains the Autofilter down-arrow for column A
> *************************************************************
> Function FilterCriteria(Rng As Range) As String
>     'By Stephen Bullen via j-walk.com
>     Dim Filter As String
>     Filter = ""
>     On Error GoTo Finish
>     With Rng.Parent.AutoFilter
>         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
>         With .Filters(Rng.Column - .Range.Column + 1)
>             If Not .On Then GoTo Finish
>             Filter = .Criteria1
>             Select Case .Operator
>                 Case xlAnd
>                     Filter = Filter & " AND " & .Criteria2
>                 Case xlOr
>                     Filter = Filter & " OR " & .Criteria2
>             End Select
>         End With
>     End With
> Finish:
>     FilterCriteria = Filter
> End Function
> *************************************************************
> 
> Also, why does this formula not refresh as do other formulas in the
> worksheet?  Is it because this particlar Function must be "force-called" for
> each refresh?
> 
> TIA Dennis

-- 

Dave Peterson
0
ec357201 (5290)
3/12/2005 5:26:34 PM
You could add
Application.Volatile
at the start.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Dennis" <Dennis@discussions.microsoft.com> wrote in message
news:73D83A15-BC7C-4C31-B612-6659328A8D05@microsoft.com...
> Using XL 2003 & 2000
>
> OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.
>
> That said, apparently the only way to refresh the data in the display cell
> is to re-activate the formula by pressing <ENTER> in the Fx Box.
>
> Can the display cells be made to refresh in real-time or, second best, by
F9?
>
> FYI (Background Info):
>
> ************************************************************II placed this
> formula into cell A1:  =FilterCriteria(A3)
> Cell A3 contains the Autofilter down-arrow for column A
> *************************************************************
> Function FilterCriteria(Rng As Range) As String
>     'By Stephen Bullen via j-walk.com
>     Dim Filter As String
>     Filter = ""
>     On Error GoTo Finish
>     With Rng.Parent.AutoFilter
>         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
>         With .Filters(Rng.Column - .Range.Column + 1)
>             If Not .On Then GoTo Finish
>             Filter = .Criteria1
>             Select Case .Operator
>                 Case xlAnd
>                     Filter = Filter & " AND " & .Criteria2
>                 Case xlOr
>                     Filter = Filter & " OR " & .Criteria2
>             End Select
>         End With
>     End With
> Finish:
>     FilterCriteria = Filter
> End Function
> *************************************************************
>
> Also, why does this formula not refresh as do other formulas in the
> worksheet?  Is it because this particlar Function must be "force-called"
for
> each refresh?
>
> TIA Dennis


0
bob.phillips1 (6510)
3/12/2005 5:26:35 PM
But changing the filter criteria forces a recalc, and that is what drives
the function, so that should be fine.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:4233264A.46494403@netscapeXSPAM.com...
> You could make your formula look like:
>
> =filtercriteria(A1)&TEXT(RAND(),"")
>
> or you could add "application.volatile" to your code.
>
> Option Explicit
> Function FilterCriteria(Rng As Range) As String
>     'By Stephen Bullen via j-walk.com
>     Application.Volatile
>     Dim Filter As String
>
> But the bad thing is that either way, the formula won't recalculate until
excel
> recalculates.
>
> Hit F9 to force a recalc before you trust the value in the cell.
>
> Dennis wrote:
> >
> > Using XL 2003 & 2000
> >
> > OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.
> >
> > That said, apparently the only way to refresh the data in the display
cell
> > is to re-activate the formula by pressing <ENTER> in the Fx Box.
> >
> > Can the display cells be made to refresh in real-time or, second best,
by F9?
> >
> > FYI (Background Info):
> >
> > ************************************************************II placed
this
> > formula into cell A1:  =FilterCriteria(A3)
> > Cell A3 contains the Autofilter down-arrow for column A
> > *************************************************************
> > Function FilterCriteria(Rng As Range) As String
> >     'By Stephen Bullen via j-walk.com
> >     Dim Filter As String
> >     Filter = ""
> >     On Error GoTo Finish
> >     With Rng.Parent.AutoFilter
> >         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
> >         With .Filters(Rng.Column - .Range.Column + 1)
> >             If Not .On Then GoTo Finish
> >             Filter = .Criteria1
> >             Select Case .Operator
> >                 Case xlAnd
> >                     Filter = Filter & " AND " & .Criteria2
> >                 Case xlOr
> >                     Filter = Filter & " OR " & .Criteria2
> >             End Select
> >         End With
> >     End With
> > Finish:
> >     FilterCriteria = Filter
> > End Function
> > *************************************************************
> >
> > Also, why does this formula not refresh as do other formulas in the
> > worksheet?  Is it because this particlar Function must be "force-called"
for
> > each refresh?
> >
> > TIA Dennis
>
> -- 
>
> Dave Peterson


0
bob.phillips1 (6510)
3/12/2005 5:38:47 PM
BTW, I would make this slight amendment so that it doesn't show blank when
no filter is applied (personal preference)

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
    Application.Volatile
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then
                Filter = "All"
                GoTo Finish
            End If
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " AND " & .Criteria2
                Case xlOr
                    Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = Filter
End Function



-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eg8fliyJFHA.2936@TK2MSFTNGP15.phx.gbl...
> You could add
> Application.Volatile
> at the start.
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Dennis" <Dennis@discussions.microsoft.com> wrote in message
> news:73D83A15-BC7C-4C31-B612-6659328A8D05@microsoft.com...
> > Using XL 2003 & 2000
> >
> > OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
> Function.
> >
> > That said, apparently the only way to refresh the data in the display
cell
> > is to re-activate the formula by pressing <ENTER> in the Fx Box.
> >
> > Can the display cells be made to refresh in real-time or, second best,
by
> F9?
> >
> > FYI (Background Info):
> >
> > ************************************************************II placed
this
> > formula into cell A1:  =FilterCriteria(A3)
> > Cell A3 contains the Autofilter down-arrow for column A
> > *************************************************************
> > Function FilterCriteria(Rng As Range) As String
> >     'By Stephen Bullen via j-walk.com
> >     Dim Filter As String
> >     Filter = ""
> >     On Error GoTo Finish
> >     With Rng.Parent.AutoFilter
> >         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
> >         With .Filters(Rng.Column - .Range.Column + 1)
> >             If Not .On Then GoTo Finish
> >             Filter = .Criteria1
> >             Select Case .Operator
> >                 Case xlAnd
> >                     Filter = Filter & " AND " & .Criteria2
> >                 Case xlOr
> >                     Filter = Filter & " OR " & .Criteria2
> >             End Select
> >         End With
> >     End With
> > Finish:
> >     FilterCriteria = Filter
> > End Function
> > *************************************************************
> >
> > Also, why does this formula not refresh as do other formulas in the
> > worksheet?  Is it because this particlar Function must be "force-called"
> for
> > each refresh?
> >
> > TIA Dennis
>
>


0
bob.phillips1 (6510)
3/12/2005 5:45:33 PM
I used xl2003 and did Data|Filter|showall (slightly different than changing the
filter, though).

The old criteria still showed up.  Hitting F9 made it disappear.

Bob Phillips wrote:
> 
> But changing the filter criteria forces a recalc, and that is what drives
> the function, so that should be fine.
> 
> --
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:4233264A.46494403@netscapeXSPAM.com...
> > You could make your formula look like:
> >
> > =filtercriteria(A1)&TEXT(RAND(),"")
> >
> > or you could add "application.volatile" to your code.
> >
> > Option Explicit
> > Function FilterCriteria(Rng As Range) As String
> >     'By Stephen Bullen via j-walk.com
> >     Application.Volatile
> >     Dim Filter As String
> >
> > But the bad thing is that either way, the formula won't recalculate until
> excel
> > recalculates.
> >
> > Hit F9 to force a recalc before you trust the value in the cell.
> >
> > Dennis wrote:
> > >
> > > Using XL 2003 & 2000
> > >
> > > OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
> Function.
> > >
> > > That said, apparently the only way to refresh the data in the display
> cell
> > > is to re-activate the formula by pressing <ENTER> in the Fx Box.
> > >
> > > Can the display cells be made to refresh in real-time or, second best,
> by F9?
> > >
> > > FYI (Background Info):
> > >
> > > ************************************************************II placed
> this
> > > formula into cell A1:  =FilterCriteria(A3)
> > > Cell A3 contains the Autofilter down-arrow for column A
> > > *************************************************************
> > > Function FilterCriteria(Rng As Range) As String
> > >     'By Stephen Bullen via j-walk.com
> > >     Dim Filter As String
> > >     Filter = ""
> > >     On Error GoTo Finish
> > >     With Rng.Parent.AutoFilter
> > >         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
> > >         With .Filters(Rng.Column - .Range.Column + 1)
> > >             If Not .On Then GoTo Finish
> > >             Filter = .Criteria1
> > >             Select Case .Operator
> > >                 Case xlAnd
> > >                     Filter = Filter & " AND " & .Criteria2
> > >                 Case xlOr
> > >                     Filter = Filter & " OR " & .Criteria2
> > >             End Select
> > >         End With
> > >     End With
> > > Finish:
> > >     FilterCriteria = Filter
> > > End Function
> > > *************************************************************
> > >
> > > Also, why does this formula not refresh as do other formulas in the
> > > worksheet?  Is it because this particlar Function must be "force-called"
> for
> > > each refresh?
> > >
> > > TIA Dennis
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
ec357201 (5290)
3/12/2005 6:12:21 PM
Don't have 2003 Dave, so I can't play with that.

If only I had a real reason to get it :-)

Regards

Bob

BTW I liked the TEXT technique.


"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:42333105.EB74664D@netscapeXSPAM.com...
> I used xl2003 and did Data|Filter|showall (slightly different than
changing the
> filter, though).
>
> The old criteria still showed up.  Hitting F9 made it disappear.
>
> Bob Phillips wrote:
> >
> > But changing the filter criteria forces a recalc, and that is what
drives
> > the function, so that should be fine.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > news:4233264A.46494403@netscapeXSPAM.com...
> > > You could make your formula look like:
> > >
> > > =filtercriteria(A1)&TEXT(RAND(),"")
> > >
> > > or you could add "application.volatile" to your code.
> > >
> > > Option Explicit
> > > Function FilterCriteria(Rng As Range) As String
> > >     'By Stephen Bullen via j-walk.com
> > >     Application.Volatile
> > >     Dim Filter As String
> > >
> > > But the bad thing is that either way, the formula won't recalculate
until
> > excel
> > > recalculates.
> > >
> > > Hit F9 to force a recalc before you trust the value in the cell.
> > >
> > > Dennis wrote:
> > > >
> > > > Using XL 2003 & 2000
> > > >
> > > > OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
> > Function.
> > > >
> > > > That said, apparently the only way to refresh the data in the
display
> > cell
> > > > is to re-activate the formula by pressing <ENTER> in the Fx Box.
> > > >
> > > > Can the display cells be made to refresh in real-time or, second
best,
> > by F9?
> > > >
> > > > FYI (Background Info):
> > > >
> > > > ************************************************************II
placed
> > this
> > > > formula into cell A1:  =FilterCriteria(A3)
> > > > Cell A3 contains the Autofilter down-arrow for column A
> > > > *************************************************************
> > > > Function FilterCriteria(Rng As Range) As String
> > > >     'By Stephen Bullen via j-walk.com
> > > >     Dim Filter As String
> > > >     Filter = ""
> > > >     On Error GoTo Finish
> > > >     With Rng.Parent.AutoFilter
> > > >         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
> > > >         With .Filters(Rng.Column - .Range.Column + 1)
> > > >             If Not .On Then GoTo Finish
> > > >             Filter = .Criteria1
> > > >             Select Case .Operator
> > > >                 Case xlAnd
> > > >                     Filter = Filter & " AND " & .Criteria2
> > > >                 Case xlOr
> > > >                     Filter = Filter & " OR " & .Criteria2
> > > >             End Select
> > > >         End With
> > > >     End With
> > > > Finish:
> > > >     FilterCriteria = Filter
> > > > End Function
> > > > *************************************************************
> > > >
> > > > Also, why does this formula not refresh as do other formulas in the
> > > > worksheet?  Is it because this particlar Function must be
"force-called"
> > for
> > > > each refresh?
> > > >
> > > > TIA Dennis
> > >
> > > --
> > >
> > > Dave Peterson
>
> -- 
>
> Dave Peterson


0
bob.phillips1 (6510)
3/12/2005 6:19:42 PM
Did Data|filter|Showall cause a recalc in the version you're using?

Bob Phillips wrote:
> 
> Don't have 2003 Dave, so I can't play with that.
> 
> If only I had a real reason to get it :-)
> 
> Regards
> 
> Bob
> 
> BTW I liked the TEXT technique.
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:42333105.EB74664D@netscapeXSPAM.com...
> > I used xl2003 and did Data|Filter|showall (slightly different than
> changing the
> > filter, though).
> >
> > The old criteria still showed up.  Hitting F9 made it disappear.
> >
> > Bob Phillips wrote:
> > >
> > > But changing the filter criteria forces a recalc, and that is what
> drives
> > > the function, so that should be fine.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > > news:4233264A.46494403@netscapeXSPAM.com...
> > > > You could make your formula look like:
> > > >
> > > > =filtercriteria(A1)&TEXT(RAND(),"")
> > > >
> > > > or you could add "application.volatile" to your code.
> > > >
> > > > Option Explicit
> > > > Function FilterCriteria(Rng As Range) As String
> > > >     'By Stephen Bullen via j-walk.com
> > > >     Application.Volatile
> > > >     Dim Filter As String
> > > >
> > > > But the bad thing is that either way, the formula won't recalculate
> until
> > > excel
> > > > recalculates.
> > > >
> > > > Hit F9 to force a recalc before you trust the value in the cell.
> > > >
> > > > Dennis wrote:
> > > > >
> > > > > Using XL 2003 & 2000
> > > > >
> > > > > OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
> > > Function.
> > > > >
> > > > > That said, apparently the only way to refresh the data in the
> display
> > > cell
> > > > > is to re-activate the formula by pressing <ENTER> in the Fx Box.
> > > > >
> > > > > Can the display cells be made to refresh in real-time or, second
> best,
> > > by F9?
> > > > >
> > > > > FYI (Background Info):
> > > > >
> > > > > ************************************************************II
> placed
> > > this
> > > > > formula into cell A1:  =FilterCriteria(A3)
> > > > > Cell A3 contains the Autofilter down-arrow for column A
> > > > > *************************************************************
> > > > > Function FilterCriteria(Rng As Range) As String
> > > > >     'By Stephen Bullen via j-walk.com
> > > > >     Dim Filter As String
> > > > >     Filter = ""
> > > > >     On Error GoTo Finish
> > > > >     With Rng.Parent.AutoFilter
> > > > >         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
> > > > >         With .Filters(Rng.Column - .Range.Column + 1)
> > > > >             If Not .On Then GoTo Finish
> > > > >             Filter = .Criteria1
> > > > >             Select Case .Operator
> > > > >                 Case xlAnd
> > > > >                     Filter = Filter & " AND " & .Criteria2
> > > > >                 Case xlOr
> > > > >                     Filter = Filter & " OR " & .Criteria2
> > > > >             End Select
> > > > >         End With
> > > > >     End With
> > > > > Finish:
> > > > >     FilterCriteria = Filter
> > > > > End Function
> > > > > *************************************************************
> > > > >
> > > > > Also, why does this formula not refresh as do other formulas in the
> > > > > worksheet?  Is it because this particlar Function must be
> "force-called"
> > > for
> > > > > each refresh?
> > > > >
> > > > > TIA Dennis
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
ec357201 (5290)
3/12/2005 7:15:18 PM
Lol. I assumed the Showall was a 2003 new feature. I have never used it
myself, I always click the dropdown and select (All).

You are correct though, it doesn't force a recalc. I find that a bit odd, as
the way that I do it does force a recalc?

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:42333FC6.9D399FC6@netscapeXSPAM.com...
> Did Data|filter|Showall cause a recalc in the version you're using?
>
> Bob Phillips wrote:
> >
> > Don't have 2003 Dave, so I can't play with that.
> >
> > If only I had a real reason to get it :-)
> >
> > Regards
> >
> > Bob
> >
> > BTW I liked the TEXT technique.
> >
> > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > news:42333105.EB74664D@netscapeXSPAM.com...
> > > I used xl2003 and did Data|Filter|showall (slightly different than
> > changing the
> > > filter, though).
> > >
> > > The old criteria still showed up.  Hitting F9 made it disappear.
> > >
> > > Bob Phillips wrote:
> > > >
> > > > But changing the filter criteria forces a recalc, and that is what
> > drives
> > > > the function, so that should be fine.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > > > news:4233264A.46494403@netscapeXSPAM.com...
> > > > > You could make your formula look like:
> > > > >
> > > > > =filtercriteria(A1)&TEXT(RAND(),"")
> > > > >
> > > > > or you could add "application.volatile" to your code.
> > > > >
> > > > > Option Explicit
> > > > > Function FilterCriteria(Rng As Range) As String
> > > > >     'By Stephen Bullen via j-walk.com
> > > > >     Application.Volatile
> > > > >     Dim Filter As String
> > > > >
> > > > > But the bad thing is that either way, the formula won't
recalculate
> > until
> > > > excel
> > > > > recalculates.
> > > > >
> > > > > Hit F9 to force a recalc before you trust the value in the cell.
> > > > >
> > > > > Dennis wrote:
> > > > > >
> > > > > > Using XL 2003 & 2000
> > > > > >
> > > > > > OK, with help, I was able to utilize Steve Bullen's
FilterCriteria()
> > > > Function.
> > > > > >
> > > > > > That said, apparently the only way to refresh the data in the
> > display
> > > > cell
> > > > > > is to re-activate the formula by pressing <ENTER> in the Fx Box.
> > > > > >
> > > > > > Can the display cells be made to refresh in real-time or, second
> > best,
> > > > by F9?
> > > > > >
> > > > > > FYI (Background Info):
> > > > > >
> > > > > > ************************************************************II
> > placed
> > > > this
> > > > > > formula into cell A1:  =FilterCriteria(A3)
> > > > > > Cell A3 contains the Autofilter down-arrow for column A
> > > > > > *************************************************************
> > > > > > Function FilterCriteria(Rng As Range) As String
> > > > > >     'By Stephen Bullen via j-walk.com
> > > > > >     Dim Filter As String
> > > > > >     Filter = ""
> > > > > >     On Error GoTo Finish
> > > > > >     With Rng.Parent.AutoFilter
> > > > > >         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
> > > > > >         With .Filters(Rng.Column - .Range.Column + 1)
> > > > > >             If Not .On Then GoTo Finish
> > > > > >             Filter = .Criteria1
> > > > > >             Select Case .Operator
> > > > > >                 Case xlAnd
> > > > > >                     Filter = Filter & " AND " & .Criteria2
> > > > > >                 Case xlOr
> > > > > >                     Filter = Filter & " OR " & .Criteria2
> > > > > >             End Select
> > > > > >         End With
> > > > > >     End With
> > > > > > Finish:
> > > > > >     FilterCriteria = Filter
> > > > > > End Function
> > > > > > *************************************************************
> > > > > >
> > > > > > Also, why does this formula not refresh as do other formulas in
the
> > > > > > worksheet?  Is it because this particlar Function must be
> > "force-called"
> > > > for
> > > > > > each refresh?
> > > > > >
> > > > > > TIA Dennis
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> -- 
>
> Dave Peterson


0
bob.phillips1 (6510)
3/12/2005 8:08:43 PM
Strange <> surprising in excel. 
<vbg>

I actually added the "show all" button to a toolbar.  I was tired of going to
each filter and choosing All (or clicking on data|Filter|showall) each time I
wanted to see, er, all the data.



Bob Phillips wrote:
> 
> Lol. I assumed the Showall was a 2003 new feature. I have never used it
> myself, I always click the dropdown and select (All).
> 
> You are correct though, it doesn't force a recalc. I find that a bit odd, as
> the way that I do it does force a recalc?
> 
> --
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:42333FC6.9D399FC6@netscapeXSPAM.com...
> > Did Data|filter|Showall cause a recalc in the version you're using?
> >
> > Bob Phillips wrote:
> > >
> > > Don't have 2003 Dave, so I can't play with that.
> > >
> > > If only I had a real reason to get it :-)
> > >
> > > Regards
> > >
> > > Bob
> > >
> > > BTW I liked the TEXT technique.
> > >
> > > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > > news:42333105.EB74664D@netscapeXSPAM.com...
> > > > I used xl2003 and did Data|Filter|showall (slightly different than
> > > changing the
> > > > filter, though).
> > > >
> > > > The old criteria still showed up.  Hitting F9 made it disappear.
> > > >
> > > > Bob Phillips wrote:
> > > > >
> > > > > But changing the filter criteria forces a recalc, and that is what
> > > drives
> > > > > the function, so that should be fine.
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > > > > news:4233264A.46494403@netscapeXSPAM.com...
> > > > > > You could make your formula look like:
> > > > > >
> > > > > > =filtercriteria(A1)&TEXT(RAND(),"")
> > > > > >
> > > > > > or you could add "application.volatile" to your code.
> > > > > >
> > > > > > Option Explicit
> > > > > > Function FilterCriteria(Rng As Range) As String
> > > > > >     'By Stephen Bullen via j-walk.com
> > > > > >     Application.Volatile
> > > > > >     Dim Filter As String
> > > > > >
> > > > > > But the bad thing is that either way, the formula won't
> recalculate
> > > until
> > > > > excel
> > > > > > recalculates.
> > > > > >
> > > > > > Hit F9 to force a recalc before you trust the value in the cell.
> > > > > >
> > > > > > Dennis wrote:
> > > > > > >
> > > > > > > Using XL 2003 & 2000
> > > > > > >
> > > > > > > OK, with help, I was able to utilize Steve Bullen's
> FilterCriteria()
> > > > > Function.
> > > > > > >
> > > > > > > That said, apparently the only way to refresh the data in the
> > > display
> > > > > cell
> > > > > > > is to re-activate the formula by pressing <ENTER> in the Fx Box.
> > > > > > >
> > > > > > > Can the display cells be made to refresh in real-time or, second
> > > best,
> > > > > by F9?
> > > > > > >
> > > > > > > FYI (Background Info):
> > > > > > >
> > > > > > > ************************************************************II
> > > placed
> > > > > this
> > > > > > > formula into cell A1:  =FilterCriteria(A3)
> > > > > > > Cell A3 contains the Autofilter down-arrow for column A
> > > > > > > *************************************************************
> > > > > > > Function FilterCriteria(Rng As Range) As String
> > > > > > >     'By Stephen Bullen via j-walk.com
> > > > > > >     Dim Filter As String
> > > > > > >     Filter = ""
> > > > > > >     On Error GoTo Finish
> > > > > > >     With Rng.Parent.AutoFilter
> > > > > > >         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
> > > > > > >         With .Filters(Rng.Column - .Range.Column + 1)
> > > > > > >             If Not .On Then GoTo Finish
> > > > > > >             Filter = .Criteria1
> > > > > > >             Select Case .Operator
> > > > > > >                 Case xlAnd
> > > > > > >                     Filter = Filter & " AND " & .Criteria2
> > > > > > >                 Case xlOr
> > > > > > >                     Filter = Filter & " OR " & .Criteria2
> > > > > > >             End Select
> > > > > > >         End With
> > > > > > >     End With
> > > > > > > Finish:
> > > > > > >     FilterCriteria = Filter
> > > > > > > End Function
> > > > > > > *************************************************************
> > > > > > >
> > > > > > > Also, why does this formula not refresh as do other formulas in
> the
> > > > > > > worksheet?  Is it because this particlar Function must be
> > > "force-called"
> > > > > for
> > > > > > > each refresh?
> > > > > > >
> > > > > > > TIA Dennis
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
ec357201 (5290)
3/12/2005 9:41:06 PM
Reply:

Similar Artilces:

Remote data access
As a new .net developer, I would like to know how a VB.net Windows application can access a SQL Server database residing on a web server. In other words, using the Visual Studio IDE, is there a way to develop a Visual Basic, Windows application that can access a SQL Server database over the internet. Thanks for suggestions, John C. John C. wrote: > As a new .net developer, I would like to know how a > VB.net Windows application can access a SQL Server > database residing on a web server. > > In other words, using the Visual Studio IDE, is there a > way to develop...

Data Validation #12
Hi. I have data validations in sheet1 as named range lists from sheet2. When I have deleted sheet2 and copy sheet2 from identical other workbook - my validations does is not work... I see Name ranges in copied worksheet, but when I use they in validation - I have error: "The Source currently evaluates an error. Do you wish continue?" Only after deleting of Named ranges in Sheet2 and recreating its, my validations works. It is possible after replacing of worksheet with named ranges for validations (named ranges names are identical) use copied worksheet without recreating ...

Maked email for deletion
I have Exhcnage 2003 in a Windows SBS 2003, also I have Norton Anitivrus for Exchange .. my problem is that Norton Maked the eamil for deletion but the users get the eamil before it is deleted with a TXT attachment message wiht the following text inside : "Small Business Server has removed potentially unsafe e-mail attachment(s) from this message: document_excel.pif Because computer viruses are commonly spread through files attached to e-mail messages, certain types of files will not be delivered to your mailbox. For more information, contact the person responsible for your network.&quo...

Data Entry Form 03-20-08
Hi, I created a form to use for viewing currently records and entering new data. This time, the form doesn't allow me to enter new data. In fact, all text boxes didn't let me to enter new information. Please help Thanks Chi Several things to check: In form properties: Allow Additions = Yes Allow Edits = Yes If the form's record source is a query, be sure the query is updatable. -- Dave Hargis, Microsoft Access MVP "Chi" wrote: > Hi, > > I created a form to use for viewing currently records and entering new data. > > This time, the ...

automation in ms access 2003; automatically makes weekly recurrenc
When using automation in ms access 2003 to be able to send appointments to Outlook, each time I add an appointment, it automatically sets a weekly recurrence schedule in Outlook. What can change in the following script to keep it from recurring, so that it will only appear as a single, one-time appointment? Private Sub cmdAddAppt_Click() On Error GoTo Add_Err 'Save record first to be sure required fields are filled. DoCmd.RunCommand acCmdSaveRecord 'Exit the procedure if appointment has been added to Outlook. If Me!AddedToOutlook = True Then MsgBox &qu...

Time question
I need a formula that would figure out what time it would have to be to complete a 40 hour week The information I have to plug in is this. Current total hours - 37 hrs 32 mins Current time is - 12:39 pm What time does it have to be to complete 40 hours? Seems like it should be simple but.... -- gueyo A1: 37:32:00 B1: 40:00:00 C1: 12:39 PM D1: =C1+(B1-A1) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "gueyo" wrote: > I need a formula that w...

Sorting lots of data
We are trying to sort through a csv file that we converted to an excel spreadsheet. the spreadsheet has over 40,000 entries which include mailing addresses that are in some cases duplicated. is there anyway to sort/filter the info to locate the duplicates and then remove them?? any help would be greatly appreciated. thank you for your responses. Hi use 'Data - Advanced Filters' and check 'unique entries' or use the following addin for this: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany miss lynar wrote: > We are trying to sort thr...

Stacking Data??
if I have data like this A B C D... 1 Type\Date 01/10/04 02/10/04 03/10/04... 2 T-X 100 50 100 3 T-Y 50 70 0 4 T-Z 400 0 400 ... .. ...

Excel Time Problem
I exported a file from a telephone switch reporting system as a "tab separated text" file, because when I export the data in Excel I have to deal with merged cells and other formatting problems. I have a couple of columns that have time values like 160:02:05 for logged in time, and 34:16:46 as the waiting dime� Both are time �DURATIONS� or time in a state. I used the Format, Cell, custom and [ss] to get the duration value to seconds, and I get 123406 and 576125 respectively. I then use these numbers in a calculation typically like 1338 / (576125 - 123406) and I get 219 as the an...

How do make a line correspond to date
I have a chart with horizontal bars and I want to put a vertical (trend) date line that corresponds to today's date. -- DMM http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:ADC1BD32-DF02-4A7F-B074-DCBBA9D0AAFD@microsoft.com... >I have a chart with horizontal bars and I want to put a vertical (trend) >date > line that corresponds to today's...

order data by a rank
i need to create a form order by the militaty rank.This form is based in a query "qrystaff" with three fields: MemberID FullName and Rank.I´d like that the data were ordered firstly by MilitaryRank and secondly by FullName in alphabetical order.There is an easy way to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 When you create queries, you're given the option to specify the sort order under each field. If the fields aren't in the order from left-to-right in the query grid, you can add ...

Turn data #2
Is there a way to turn the data in an Excel spreadsheet so that what is on the top is now on the side and what is on the side is now on top? I need to export my spreadsheet into Access but, I can't get it to format correctly in its current orientation. ~Make a backup copy of your data to work from (just in case, so you don't lose data) ~Highlight the entire range of data; click >Edit >Copy ~Choose a new location, on a different sheet perhaps; click >Edit >Paste Special and select the Transpose box; click OK Does that do it for you? thank you so much. You are a genius...

Pivot Table Refresh Problems
Hi All: Hoping that someone can help. I am trying to refresh a pivot table using the following code which was inserted on the Daily Production Output Sheet(both sheets in the same workbook). I am using Excel 2003 Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Daily Production Output").PivotTables("PivotTable3").RefreshTable End Sub The Calculation has been set to Automatic. However when I try to run the above code I am getting the following error" Run Time error 1044, Application defined or Object defin...

Date/Time Modified
Hello, My Access Database consists of many tables. The Query attached to my main Client Information Form is made up of 4 tables. -Client Table -Account Table -Batch No Table -Advisor Table Basically, I want to be able to insert a Date/Time Modified record at the top of my main form that will show a data modified if any record is changed/updated in any of these tables. Is this possible? Thank you, Mellissa -- Message posted via http://www.accessmonster.com ...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Error in posting 4 times! Frustrating problems with html signatures and styles (normal.dot)
I just learned that the message posted 4 times; I thought I had canceled the previous posts where I had not filled in the right newsgroups.... On my machine, it only shows posted 1 time; but I guess that the news server does not support cancellations of posts. Sorry about that...... ...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Is there a work-a-round i can use for making a datagrid....
Hey all, I am trying to make a datagrid on a form that the user would input info, and once completed, it would be put in correct area's of the workbook. I cant use a datagrid as job doesn't alw downloads for net so i cant get the update needed to alw me to use datagrids( see post sugj: "Missing Control In Addin") is there a way i can use a text box or some other box or tool to do this? When you say "form", do you mean UserForm or worksheet set up to look like a data input form? If a UserForm, then yes, you can use TextBoxes, but you didn't supp...

Display of CRM data in SharePoint
Is is possible to allow non-CRM users the ability to view CRM data on SharePoint without using the CRM external connector? Thanks, -Rick M. Microsoft would require you to have a license per user I think ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Rick M" <RickM@discussions.microsoft.com> wrote in message news:B3E731A1-D059-4D49-B879-6025A1817FD8@microsoft.com... > Is is possible to allow non-CRM users the ability to view CRM data on > SharePoint without using the CRM external connector? > > Thanks, > -Rick...

Outlook Data File Not Closing Properly
I always get the following error message when starting Outlook 2002 (running Office XP and Windows XP) : "The data file 'outlook' was not closed properly. The file is being checked for problems." I get this message everytime (whether I hibernate or shut down) when I start Outlook. This checking of the file can take anywhere from 30 seconds to 5 minutes and is very frustrating. Anyone have any idea what the issue is ? This started happening just after I installed XP SP3. ...

BP Installation Error
Someone post this question before:- MbfPackager data importation process has failed. please c:\DOCUME~1\ADMIN~1\LOCALS~1\Temp\MbfPackager.Import.NoMerge.Common.xml for more information. Problem: When installing Microsoft Business Portal 2.5. Can anyone help me on this error? I'm installing Business Portal 2.5 Feature Pack on Windows 2003 Standard Edition. And, the BP is reading the Great Plains 8.0 database at another server. Cheers, Emily ...

Evey time I create a new document and save it, it defaults to template
I am using excel 2003. Evey time I create a new document and save it, it defaults to template - xlt. To create a document I just do new -> blank document. How can i fix this? thanks Tools>Options>Transition. Save Excel Files As. Change to MS Excel Workbook. Gord Dibben MS Excel MVP On Fri, 5 Feb 2010 17:54:26 -0600, "Greg" <greg@nospam.com> wrote: >I am using excel 2003. >Evey time I create a new document and save it, it defaults to template - >xlt. >To create a document I just do new -> blank document. > >How...

Access data
Is there a way to open an Access 97 database with Excel 97? Hi C, I have a hyperlink in my excel workbook that opens the access workbook, i use it for correction purposes. Would a hyperlink work? Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=492101 In Excel try Data>Import External Data>Import Data or New Database Query Gord Dibben Excel MVP On Fri, 9 Dec 2005 07:43:...

Importing data from Excel to Outlook
I have an excel spreadsheet with over 2500 contacts. The Spreadsheet is set up with Last Name, First Name in Column A, Full Address in Column B, Email Address in Column C. I have highlighted and defined a name for each of the columns. Each time I attempt to import the data into Outlook 2003 the importing and exporting window never completes its task. I tried to perform an import with a new excel workbook with only limited information and the same thing happens. Any ideas? I've had the same problem. First, save your file as a text file, then import into Outlook. HTH -- Since...

How to make the title bars in MDI's MainFrame and ChildFrame narrower?
I want to have as much space for multiple views in MDI application. The title bar for each child frame (plus the title bar for MainFrame itself ) take too much space. I removed the title bar for a child frame by removing WS_CAPTION in window style - however the children/views do not want to tile in this approach. At least, is there any way to squeeze the width to absolute minimum, for both ChildFrame and MainFrame? I use MFC 6.0 on WinXP. You cant get away without painting the Nonclient area yourself if you want to do this. You will need to override NCCalcSize as well. Its a good amou...