Summary: Learn about the increased worksheet capacity in Microsoft Office Excel 2007 and techniques that you can use as you design and create worksheets to improve calculation performance. (40 printed pages)
Charles Williams, Decision Models Limited
October 2006
Applies to: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002, Microsoft Excel 2000
Contents
=REPLACE(LEFT(CELL("filename",A2),FIND("]",CELL("filename",A2))-5),1,FIND("[",CELL("filename",A2)),"")or possibly better
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-5-SEARCH("[",CELL("filename")))
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub
=CELL("row")=ROW()
"Excel's AutoFilter feature definitely ranks right up there when it comes to handy tools. This feature, which you access with the Data, Filter, AutoFilter command, works with a range of cells set up as a database or list. When AutoFiltering is turned on, the row headers display drop-down arrows that let you specify criteria (such as "Age greater than 30"). Rows that don't match your criteria are hidden, but they are redisplayed when you turn off AutoFiltering.
'One problem with AutoFiltering is that you can't tell which criteria are in effect. Stephen Bullen developed a custom VBA worksheet function that displays the current AutoFilter criteria in a cell. The instructions that follow are for Excel 97 or later.
'Press Alt+F11 and insert a new module for the active workbook. Then enter the VBA code for the FilterCriteria shown below.
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
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
'After you've entered the VBA code, you can use the function in your formulas. The single-cell argument for the FilterCriteria function can refer to any cell within the column of interest. The formula will return the current AutoFilter criteria (if any) for the specified column. When you turn AutoFiltering off, the formulas don't display anything.
'The figure below shows the FilterCriteria in action. The function is used in the cells in row 1. For example, cell A1 contains this formula:
=FilterCriteria(A3)
'As you can see, the list is currently filtered to show rows in which column A contains January, column C contains a code of A or B, and column D contains a value greater than 125 (column B is not filtered, so the formula in cell B1 displays nothing). The rows that don't match these criteria are hidden."
Function FilterOn(myCell As Range) As Boolean
On Error Resume Next
With myCell.Parent.AutoFilter
With .Filters(myCell.Column - .Range.Column + 1)
If .On Then FilterOn = True
End With
End With
End Function
