Get Around Excel's 7 Nested IF Limit. VBA and AutoFilters

Excel's 7 Nested Function Limit

See Also Lookup Method Here

It's quite common for users to encounter the 7 nested Functions in Excel. For those not sure, a nested function is where we use the result of one function as the argument for another.

If your functions are all IF Functions, then see Lookup Method . This by far the most efficient and flexible way to get around the limit on IF functions.

Ok, so let's jump in with an example of where you may use this other method which also gets around the limit.

Let's say you have a column of data (Column "A"). In this column there are a total of 70 different numeric cells. That is, 1 to 70 in any order.

Based on the content of each cell, we need to perform a Tax calculation by multiplying a number by a defined constant named "Tax" based on 8 different criteria

CRITERIA

IF A1 = 1 to 7 then 7*Tax
IF A1 = 8 to 10 then 10*Tax
IF A1 = 11 to 20 then 20*Tax
IF A1 = 21 to 30 then 30*Tax
IF A1 = 31 to 40 then 40*Tax
IF A1 = 41 to 50 then 50*Tax
IF A1 = 51 to 60 then 60*Tax
IF A1 = 61 to 70 then 70*Tax

What need to do is make all the above criteria into 2 Named Formulas

Here is how, assuming we want the results in Column "B" relative to the row in Column "A".

1) Select B1 and go to Insert>Name>Define.
2) Now in the "Names in Workbook" box enter: Criteria1
3) In the "Refers to" box enter:
=IF(AND(A1>0,A1<8),7*Tax,IF(AND(A1>7,A1<11),10*Tax,IF(AND(A1>10,A1<21),20*Tax,IF(AND(A1>20,A1<31),30*Tax))))
4) Now click Add.
2) Now in the "Names in Workbook" box enter: Criteria2
3) In the "Refers to" box enter:
=iF(AND(A1>30,A1<41),40*Tax,IF(AND(A1>40,A1<51),50*Tax,IF(AND(A1>50,A1<61),60*Tax,IF(AND(A1>60,A1<71),70*Tax))))
4) Now click Add

Now we need to add the defined constant named "Tax"

1) Now in the "Names in Workbook" box enter: Tax
2) In the "Refers to" box enter: =0.15
3) Finally click Add and then OK.

Now add numbers 1 to 70 on the Worksheet Starting from A1. Use the Fill Handle (while holding down Ctrl) of A1 after entering number 1 into A1 and reselecting it.

All that is left now is to make use of our 2 named formulas that total 8 different criteria. So, in B1 simply Enter:
=IF(Criteria1,Criteria1,Criteria2)
Then copy down.

Excel Dashboard Reports & Excel Dashboard Charts

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!

In the majority of cases it's faster and more efficient to use one of Excel's built in features as apposed to re-inventing the wheel with VBA code. This is why those that have learnt Excel from the ground-up know what native features Excel has to offer. While those only familiar with VB/VBA tend to be the ones who re-invent the wheel.

Ok, the first thing we need to know is how to apply AutoFilter to a range. When we do apply AutoFilter via VBA one SHOULD always turn the off any current filters and remove them completely. Why not check if the AutoFilter is already in place and go from there? The answer is simple, while we can determine if AutoFilter has/is on a specific Worksheet, we cannot guarantee (with extra checking) that it is in use on the range we need! For example, we could use the code below to check.

From the code above we will know if AutoFilters are visible, but not necessarily in Filter mode (more on that soon). However, we cannot tell if the AutoFilterMode is applied to the correct range.Let's now see how we can determine if the AutoFilters and in use and are being used to filter down.

As you can see, we have used the FilterMode Property of the Worksheet to determine whether the AutoFilters are filtering data down.So, in summary, AutoFilterMode tells us if the AutoFilter arrows are visible and FilterMode tells us if they are in use.However, as I mentioned above this does not tell us which range has had AutoFilter applied. So, with this in mind, we are better off simply removing any existing Autofilter and then applying them to our required range. Here is how, assuming we want A1:D1 to have the AutoFilters.

Another advantage to applying AutoFilter is this manner is that no error occurs if AutoFilterMode is already false. By the way, we cannot use: AutoFilterMode = True to apply AutoFilters. To apply AutoFilter (at this time with no criteria) we would use Range("A1:D1").AutoFilter.If we are to first check the range that AutoFilter is applied to, we would use code like below;

In my mind though, this code is superfluous when compared with simply removing and applying AutoFilters.Let's now look at how we apply AutoFilter to a SINGLE cell in a range. If we had our table in the range A1:D200 on the Active sheet and we used the "ApplyAutoFilters" Procedure with .Range("A1").AutoFilter we would likely end up with AutoFilter applied to ALL contiguous headings across row 1. This due to the fact that Excel will detect the contiguous headings across row 1 and assume that we want all headings to have AutoFilters. We can force Excel to not do this by specifying a 2 row single column range. For example;

Next month we will look in detail at the AutoFilter Method. The syntax for this Method is as shown below

expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

In the interim, you may find the custom function below of use for AutoFilter
Display Excel AutoFilter Criteria

OzGrid Excel and VBA Newsletter June 2006

Newsletter Index

EXCEL TIPS AND TRICKS

EXCEL VBA TIPS AND TRICKS