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

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

Sat 25
Nov 2017

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

Sun 09
Jul 2017

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.

Tue 13
Jun 2017

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

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 -

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

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

Sat 08
Apr 2017

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.

Sat 16
Jul 2016

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.

Sun 27
Dec 2015

Part II quiz on LOOKUPs in Excel

Tue 29
Sep 2015

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

*--- 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.

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

*--- 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.