Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
You could add
Application.Volatile at the start. -- HTH RP (remove nothere from the email address if mailing direct) "Dennis" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... You could add Application.Volatile at the start. -- HTH RP (remove nothere from the email address if mailing direct) "Dennis" wrote in message ... 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 |
#6
|
|||
|
|||
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" wrote in message ... 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 |
#7
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#8
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#10
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refresh external data on a protected sheet | Excel Discussion (Misc queries) | |||
Glue/Lock cells to external data before refresh | Excel Discussion (Misc queries) | |||
Shuffling Data To Make Lists Match | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) |