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 22 Apr 2017

Tips & Tricks 159 - Prompted to "Save File" even if I haven't changed anything

By |Saturday, April 22nd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

When you open a workbook and you change something in the workbook and you close the workbook, it will ask you to save the workbook. But sometimes, even if you have not changed anything, still the workbook asks to be saved even if you haven't changed.

Point 1 - Microsoft has provided the answer to this question at following link -

(more…)

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.

Sat 06 Jun 2015

Article 15 - Dependent Data List 01 - Creating Flexible / Dynamic Dependent Data List

By |Saturday, June 06th, 2015|Categories: Articles|Tags: , , , , , , , |0 Comments

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

In this article, I want to create a dynamic dependent data list. It means that entries can be added, removed at any point of time without impacting your Dependent Data List. Without making matter complicated, this article assumes that first level is fixed and second level can keep changing. In second article of this series, I am going to remove this dependency also, hence both first and second level of entries can be removed / added in second article. The examples which I am going to show is one level of dependency but same logic can be extended to any level of dependencies.

1. Create the right layout for your dependent data list

Suppose, you have a list of 6 continents and you want to display the countries on the basis of continents selected. The step 1 is to layout your data properly.

Below, is the layout which you need to use. First row will contain the name of continents (i.e. first level drop down values) and below them will be the list of countries (i.e. second level of drop down values)

No blanks are allowed in between the list entries. Blanks can be only at the end.

Dependent Drop Down Layout

2. Create the first drop down

Let's assume that cell A1 needs to contain the first drop down.

Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put $F$1:$K$1

First Dropdown

3. Prepare the formula for second drop down

Now, it can be simplified to finding the range which contains first drop down value in  first row. For example, if I select South America, I should be able to get the range $K$2:$K$12 (I have taken $K$12 for illustration purpose only. You can have any range. The trick will be to show only the values from $K$2:$K$6 in case of South America, $H$2:$H$5 in case of Australia and so on)

The range can be located by INDEX - MATCH combination as shown below. Notice, there are two commas after $K$12 which indicates that all rows should be selected for the column in the range $F$2:$K$12

=INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))

This formula gives a range of $K$2:$K$12 for South America.

4. Create the second drop down

Let's assume that we want to create the second drop down in B1.

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

=INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))

Now, select a continent name in A1 and you will have list of countries shown in dependent drop down.

Second Dropdown with Blanks

5. Remove the blanks from second dropdown

We notice that there are blanks in second drop down at the end of the list. We need to remove these.

The logic, we will adopt is this - We have got the range in step 3. Now we need to count the non blank cells in that range and use OFFSET to get the required range.

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

Note - INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)) is first part of OFFSET and part within COUNTA making matter very simple. Hence, the formula is OFFSET("index formula",1,0,COUNTA("index formula")). In first INDEX, in place of $F$1:$K$12 we can put $F$1:$K$1 also. But as I said, I want to keep uniformity and make matter simple by having a very simple formula OFFSET("index formula",1,0,counta("index formula")).

INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)) - Gives $K$1 in case of South America, $G$1 in case of Asia and so on. But, our range needs to start from second row i.e. $K$2 in case of South America, $G$2 in case of Asia and so on. Notice 1 in bold within OFFSET, it shifts the reference by 1 row. Hence, it makes $K$1 to $K$2 and so on.

COUNTA(INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))) - Gives number of non blank cells. In case of Asia, it is 6. Hence, our reference becomes $G$2:$G$6.

6. Put the above formula in Dependent Data List.

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

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

Now, you have perfect looking dependent data list.

Second Dropdown without Blanks

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)

Sat 09 Aug 2014

Article 4 - Dynamic Charting in Excel i.e. Create a Chart which Automatically Updates when Data is Added or Removed

By |Saturday, August 09th, 2014|Categories: Articles|Tags: , , , |0 Comments

When you create a chart, you select a range. Now assume, one more row of data is added. But that will not be reflected in your chart as your chart had a fixed range.

-- The Excel for this article can be downloaded from Dynamic Charting --

A work around is to select more rows in your range beyond the existing range. i.e. if your current range is A1:B5, you can select A1:B10 where A6 to B10 are blanks. Hence, when data is added into A6 to B10, chart will automatically be updated. But there are two problems in this approach.

1. Chart shows placements for A6 to B10 also, hence your chart will have 10 data points in this case whereas actual graph will be for A1 to B5.
2. If your data goes beyond A10 that will not reflect in the chart unless you change the range again for the chart.

There are 3 approaches possible to overcome above limitations and create something called Dynamic Chart -

1. Table Approach
2. OFFSET Approach
3. Pivot Table Approach

(more…)