Sat 25 Nov 2017

VBA - Create an Index (Summary, Table of Contents) Sheet Macro

By |Saturday, November 25th, 2017|Categories: VBA|Tags: , , , , , |0 Comments

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

(more…)

Sun 09 Jul 2017

Article 46 - Creating Pivot Table with Dynamic Range

By |Sunday, July 09th, 2017|Categories: Articles|Tags: , , , , , , , , |1 Comment

The file related to this article can be downloaded from Dynamic Pivot Tables

We all make pivot tables and we also know that every time, the range of data which pivot uses goes beyond the current range, we need to change the data range. It becomes painful and also if you are creating dashboards, it is a poor design. Once you create a dashboard, anybody should be able to refresh the pivot and not worry about changing ranges.

(more…)

Tue 13 Jun 2017

Solution 62 - Produce the Sum for Merged Cells Headers

By |Tuesday, June 13th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers

Put following formula B14 and drag right and down

=SUM(OFFSET($A$1,ROWS($1:1),MATCH(B$13,$1:$1,0)-1,,IFERROR(MATCH(C$13,$1:$1,0),COUNTA($2:$2)+1)-MATCH(B$13,$1:$1,0)))

Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the workbook related to this challenge - Sum for Merged Cells

This time challenge is to produce the sum where header is merged cells. For the layout like below -

1

You need to write a single formula which can be dragged right and down to generate the sum for below table

1

The solution to this challenge will be published after a month i.e. on 13-Jun-17.

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 16 Jul 2016

Article 41 - Findings from the VLOOKUP and INDEX/MATCH shootout

By |Saturday, July 16th, 2016|Categories: Articles|Tags: , , , , , , , , , |0 Comments

Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time.

Following is the methodology to test -

1. The worksheet 100000 contains 100001 records. Column A is Numbers and column B is Text. Column C is alpha-numeric which is the result field.

(more…)

Sun 27 Dec 2015

Excel Quiz 28 - LOOKUPs - Part II

By |Sunday, December 27th, 2015|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 28 - LOOKUPs - Part II

Part II quiz on LOOKUPs in Excel

Tue 29 Sep 2015

Solution - Challenge 24 – Sum a Range Conditionally where Range Inputs are variables

By |Tuesday, September 29th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the Challenge 24 – Sum a Range Conditionally where Range Inputs are variables

Put following formula for the result -

=SUMPRODUCT((INDEX(INDIRECT(D1):INDIRECT(D2),,)>D3)*(INDEX(INDIRECT(D1):INDIRECT(D2),,)))

The workbook illustrating the solution can be downloaded from Solution - Challenge 24 – Sum a Range Conditionally where Range Inputs are variables

Sat 29 Aug 2015

Challenge 24 - Sum a Range Conditionally where Range Inputs are variables

By |Saturday, August 29th, 2015|Categories: Challenges|Tags: , , , , , |1 Comment

--- Excel related to this challenge can be downloaded from Challenge - Sum a Range Conditionally ---

Suppose, you have values in column A and D1 and D2 contains the range references. D3 contains the condition value for greater than. In this example, you will need to sum up between A4 and A8 where values are > 30.

Of course, A4, A8 and 30 are variables and driven by values in D1, D2 and D3.

Challenge - Sum a Range Conditionally

You may post your answers in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 28-Sep-15.

Fri 19 Jun 2015

Article 16 – Dependent Data List 02 – Creating Flexible / Dynamic Dependent Data List even First Drop Down is Flexible

By |Friday, June 19th, 2015|Categories: Articles|Tags: , , , , , |0 Comments

--- The Excel file related to this article can be downloaded from Dependent Drop Down List 02 ---

In Article 15, we saw that our Dependent Data list can grow / reduce vertically but we had fixed the horizontal range. Now, we will remove this restriction and our data list can grow / reduce vertically as well as horizontally.

1. Prepare the formula for first drop down.

In article 15, we saw that it is very simple i.e. $F$1:$K$1. Let's assume that maximum it can grow till Z1. In this case, we need to prepare a formula which will give us a range of $F$1:$K$1 within the range of $F$1:$Z$1 if South America is selected. If another entry is added or deleted, it should adjust appropriately.

The formula in this case would be -

=OFFSET($F$1,0,0,1,COUNTA($F$1:$Z$1))

(Though OFFSET is a volatile formula but simplicity of OFFSET is a reason to use)

2. Prepare the formula for second drop down

The formula was already there, only change is that $K is replaced by $Z. (As I said earlier 12 is a number till which the list can grow. You can put any number say 100, 900 etc.)

=OFFSET(INDEX($F$1:$Z$12,,MATCH($A$2,$F$1:$Z$1,0)),1,0,
COUNTA(INDEX($F$2:$Z$12,,MATCH($A$2,$F$1:$Z$1,0))))

3. Create the first drop down list and second drop down list utilizing above formulas.

Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET($F$1,0,0,1,COUNTA($F$1:$Z$1))

Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET(INDEX($F$1:$Z$12,,MATCH($A$2,$F$1:$Z$1,0)),1,0,
COUNTA(INDEX($F$2:$Z$12,,MATCH($A$2,$F$1:$Z$1,0))))

Now, your dynamic drop down list which can grow vertically as well as horizontally is ready.