Sat 08 Apr 2017

Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted

By |Saturday, April 08th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be lelia.vang@gmail.com as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.

(more…)

Sat 09 Jan 2016

Excel Quiz 29 – COUNT Quiz – Part 2

By |Saturday, January 09th, 2016|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 29 - COUNT Quiz - Part II

A quiz on various functions used to perform counting in Excel.

Sat 26 Dec 2015

Tips & Tricks 124 - VBA - Check if a Range is Blank When Range Contains Formulas returning Blanks

By |Saturday, December 26th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not.

WorksheetFunction.CountA(Range("A1:A100"))

Sub CheckifBlank()
    If WorksheetFunction.CountA(Range("A1:A100")) Then
        MsgBox "Range is Not Blank"
        Else
        MsgBox "Range is Blank"
    End If
End Sub

But if your range contains even a single formula which is returning blank, the above will not return the range as blank even though it contains literal blanks.

To handle this, you can use below to check if your range is blank even though the range contains formulas returning blanks.

Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100"))

Sub TestBlanks()
    If Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100")) Then
        MsgBox "All Blanks"
        Else
        MsgBox "Not All Blanks"
    End If
End Sub

 

Sat 11 Jul 2015

Excel Quiz 16 - COUNT Quiz - Part I

By |Saturday, July 11th, 2015|Categories: Quizzes|Tags: , , , , , , |0 Comments

Excel Quiz 16 - COUNT Quiz - Part I

Quiz on various COUNT functions provided by Excel. This is part I of II for COUNT Functions quizzes. This quiz covers only those Excel functions which start with the word COUNT.

Sat 16 Aug 2014

Tips & Tricks 41 - I have data for many years but I want the sum for only last 12 months

By |Saturday, August 16th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Let's assume that your data is aligned like this and you want to have sum only for last 12 months. This should take care of if future entries are done. Hence, if Jun data is entered, sum up has to start from Jun month. -

1

Use this formula -

=IFERROR(SUM(OFFSET(INDIRECT("$B"&COUNTA($A:$A)),0,0,IF(COUNTA($A:$A)-12>0,-12,-COUNTA($A:$A)+1),1)),"")

Note - If you have only one column say B, replace $A with $B. (If only A, replace $B with $A)