Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter July 2006

Search Tips

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

Dynamic Lookups in Excel You can download a working sample from the here

These are very handy for when you lookup data but cannot be sure which column your returned data should come from. In other words, users may have inserted a column within the table. The VLOOKUP is about the most popular of the Lookup functions so we will use this function for examples.

Ok, let's go with a sample using a small table of data for ease of understanding. You can download a working sample from the here

The layout of our data are headings in row 1 from A1:D1 on a Worksheet called "Data".
Names | Department | Age | Gender | Pay Rate P/H

Directly underneath these headings is related data. For simplicity though, only down to row 8.

CREATE NAMED DYNAMIC RANGES Or here for all types of Named Ranges .

NAMING THE NEEDED RANGES
*You can, if you wish, use many more rows than A1:A19,B1:B19 etc to ensure your dynamic named ranges will keep expanding*

1) While on the "Data" Worksheet Go to Insert>Name>Define.

2) In the "Names in Workbook:" box type: Data_Table.

3) In the "Refers to:" box type, or copy/paste;
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),COUNTA(Data!$1:$1))

4) Click Add but not OK, just yet.

5) Repeat steps 2, 3 and 4 using the names (no quotes) and formulas below.
"Names"
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),1)

"Department"
=OFFSET(Data!$B$1,0,0,COUNTA(Data!$B$1:$B$19),1)

"Age"
=OFFSET(Data!$C$1,0,0,COUNTA(Data!$C$1:$C$19),1)

"Gender"
=OFFSET(Data!$D$1,0,0,COUNTA(Data!$D$1:$D$19),1)

"Pay_Rate_PH"
=OFFSET(Data!$E$1,0,0,COUNTA(Data!$E$1:$E$19),1)

"Headings"
=OFFSET(Data!$A$1,0,0,1,COUNTA(Data!$1:$1))

6) Now after clicking Add for the last named range, click OK.

It is vital that there is no other data on the "Data" Worksheet other than your table of data occupying A1:E8.

Let's now show some formulas that can be used to lookup a name in the table (Data_Table) and return the "Department" they work in.

VLOOKUP WITH COLUMN()
=VLOOKUP("June K",Data_Table,COLUMN(Department),FALSE)

This formula will locate the name "June K" in the 1st column of data (Names) and return the corresponding row from the "Department" column. Should a user insert a column anywhere within, or at the end of the table, the formula will still return the correct result. However, should a column be added before Column A (making "Names" become column B) it will fail. To address this potential problem we could use Index with Match .

INDEX WITH MATCH
=INDEX(Data_Table,MATCH("June K",Names,0),MATCH("Department",Headings,0))

This formula is the most flexible as not only will it accommodate columns being added anywhere within, before or after the table, we can also use it to look left of our Names column. This may be needed when/if a column is added to the Start of our table.

VLOOKUP WITH MATCH
=VLOOKUP("June K",Data_Table,MATCH("Department",Headings,0))

Again though, as it uses VLOOKUP it will always look in the first column only of the table (Data_Table) for the name.

INTERSECTION
=June_K Department

This last one requires us to add some more named ranges, but we can do so with the aid of Excel.

1) Select the table of data but do not include any headings.

2) Go to Insert>Name>Create and ensure that ONLY "Left column" is checked and click OK.

Ok, we have now created a named range for all our names. It should be noted that, as spaces are not allowed, Excel would replace any spaces with the underscore. That is "June K" would be named "June_K".

Now we can simply Enter the Intersection formula as shown below into any cell and it will return the Department (or any other column) of the persons name we use. In this case it's "June K". It is vital to note that there IS a space between "June_K" and "Department"

=June_K Department

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

Back To: AutoFilters in Excel VBA . See Also: AutoFilters via User Interface | Display Excel AutoFilter Criteria

VBA & AutoFilters Criteria

Ok, last page we looked at how we can set AutoFilters up within an Excel Workbook. In summary, rather than check if AutoFilters are already applied to a specified Worksheet with an IF Statement, then, if they were on and in use (filtered down) we would turn them off and apply to the needed range. If they weren't on then simply apply them to the needed range.

This however was a lot of superfluous code. The easiest and best way is as shown below;

Sub ApplyAutoFilters()    With ActiveSheet            .AutoFilterMode = False            .Range("A1:D1").AutoFilter    End With    End Sub

In the code above we turn off any existing AutoFilters and apply them to the range A1:D1 of the active worksheet.

FILTERING DOWN TO SHOW 1 MATCHING CRITERIA

Let's now look at how we can apply AutoFilters and show only matching criteria. In the examples below I have used a specified Worksheet by referencing its CodeName . It is also based on the data being in the range A1:D100 with A1:D1 being headings:

Name | Age | Date Joined | Department

Sub FilterTo1Criteria()    With Sheet1            .AutoFilterMode = False            .Range("A1:D1").AutoFilter            .Range("A1:D1").AutoFilter Field:=2, Criteria1:=35    End With    End Sub

In the example below we have filtered our table down to match 1 criteria (Criteria1) on our second heading (Age) to show only those who are 35. If we were to show all those that are 35 or older our Criteria1 would need to be like;

Criteria1:=">=35"

In other words, the criteria and any operators should be passed as text with an equal sign preceding the string.

We can have the filter show only blanks for the specified Field by using: Criteria1:="="

To show all non-blanks we would use: Criteria1:="<>"

XlAutoFilterOperator can be one of these constants

xlAnd default
xlBottom10Items
xlBottom10Percent
xlOr
xlTop10Items
xlTop10Percent

If we wanted to show only those in the Name field whose name Start s with a "D" we would use: Criteria1:="=D*"
To show all names that do not contain a letter "a" we would use: Criteria1:="<>*a*"

In short, the best way to obtain your needed criteria is to simply record a macro filtering your table down and then copy the Criteria1: and the optional Criteria2: code generated.

If desired, for whatever reason, we can have Excel hide the Filter arrow for Field2 (or any Field) by using an additional
argument after Criteria1. That is: ,VisibleDropDown:=False

FILTERING DOWN TO SHOW 2 MATCHING CRITERIA

Let's now expand on the above by filtering down to show 2 criteria.

Sub FilterTo2Criteria()    With Sheet1            .AutoFilterMode = False            .Range("A1:D1").AutoFilter            .Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=35", _             Operator:=xlAnd, Criteria2:="<=45"    End With    End Sub

In the above code we have chosen to show all whose age is between 35 and 45. It's important to note that for the Operator argument we have used xlAnd. If we had used the other choice (XlOr) our results would be that of our original table. That is, all records would show as all people would be either >=35 or <=45.

Sub FilterTo2Fields()    With Sheet1            .AutoFilterMode = False                With .Range("A1:D1")                     .AutoFilter                     .AutoFilter Field:=1, Criteria1:="Dave"                     .AutoFilter Field:=4, Criteria1:="Lab"                End With    End With    End Sub

In the code above we have shown all those with the name "Dave" whose department is "Lab". As you can see from the above code,
We can add more fields, but cannot exceed our total column count of headings. In this case we could use Field 1, 2, 3 and/or 4.

FILTERING DOWN TO SHOW WILDCARDS

The wildcard characters we can use in AutoFilter are the asterisk (*) to represent a string of characters and/or the question mark (?) to represent a single character.

However, what if we need to show data that actually houses the * or ? By the way, if at all possible these characters should not be used on their own.

Sub FilterToShowAsterisk()    With Sheet1            .AutoFilterMode = False            .Range("A1:D1").AutoFilter            .Range("A1:D1").AutoFilter Field:=1, Criteria1:="~*"    End With    End Sub

Back To: AutoFilters in Excel VBA

Software Categories Search Software