Sun 09 Jul 2017

Article 46 - Creating Pivot Table with Dynamic Range

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

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.

Mon 01 May 2017

Solution - Challenge 61 - Generate Multiplication Table

By |Monday, May 01st, 2017|Categories: Solutions||0 Comments

Below is a possible solution to the challenge - Challenge 61 - Generate Multiplication Table

Put following formula and drag right and down -

=ROWS(\$1:1)*COLUMNS(\$A:A)

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

Sat 01 Apr 2017

Challenge 61 - Generate Multiplication Table

By |Saturday, April 01st, 2017|Categories: Challenges||4 Comments

This time, I want to set a challenge which is not difficult and useful for your kids.

The challenge is to write a formula which can be dragged right and down to generate a multiplication table.

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

Sat 07 Jan 2017

Tips & Tricks 153 - Sum only Visible Columns

By |Saturday, January 07th, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , , |2 Comments

You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2)

If you hide the columns, the sum stays the same whether columns are hidden or visible.

Unfortunately, Excel doesn't provide any native functionality to accomplish this. This will have to be done through a VBA function. We will write a VBA function "SumVisCols" which can be called like =SumVisCols(A2:F2)

(You can give any range not only A2:F2)

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given and change the bold lines as per your requirement
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

Tue 21 Jun 2016

Solution - Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Tuesday, June 21st, 2016|Categories: Solutions||0 Comments

Below is a possible solution to the challenge - Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet

1. Create a Sheet named Project which can hold all the projects. In that sheet, enter following formula in A2 and copy down till row 141 in columns A, B and C (as every sheet can contain a maximum of 20 projects and there are 7 days in a week and you can have a maximum of 20*7=140 rows)

Sat 21 May 2016

Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Saturday, May 21st, 2016|Categories: Challenges||0 Comments

This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday.

1. Your sheets are named Day1, Day2.....Day7.

2. One sheet can have a maximum of 20 entries.

3. The entries may not be same everyday depending upon whether a new project has started or a project has finished.

4. You need to get all the consolidation in "Consolidated Weekly Sheet".

One typical day's sheet -

You need to write formulas (or VBA) to populate row 3 onwards

A typical answer would look like -

The worksheet related to this challenge can be downloaded from Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

The answer to the above challenge will be published after a month i.e. on 21-Jun-16.

Tue 02 Feb 2016

Solution - Challenge 33 – Convert Matrix into Linear Column – II

By |Tuesday, February 02nd, 2016|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 33 – Convert Matrix into Linear Column – II.

Enter below formula and drag down -

=IF(OFFSET(\$A\$1,MOD(ROWS(\$1:1)-1,COUNTA(\$A:\$A)),ROUNDUP(ROWS(\$1:1)
/COUNTA(\$A:\$A),0)-1)=0,"",OFFSET(\$A\$1,MOD(ROWS(\$1:1)-1,COUNTA(\$A:\$A)),
ROUNDUP(ROWS(\$1:1)/COUNTA(\$A:\$A),0)-1))

The workbook containing the above solution can be downloaded from Solution - Challenge 33 – Convert Matrix into Linear Column – II.

Mon 18 Jan 2016

Solution - Challenge 32 – Convert Matrix into Linear Column – I

By |Monday, January 18th, 2016|Categories: Solutions|Tags: , , , , , |3 Comments

Below is a possible solution to Challenge 32 – Convert Matrix into Linear Column – I

Enter following formula and drag down -

=IF(OFFSET(\$A\$1,ROUNDUP(ROWS(\$1:1)/COLUMNS(\$A\$1:\$D\$100),0)-1,
MOD(ROWS(\$1:1)-1,COLUMNS(\$A\$1:\$D\$100)))=0,"",OFFSET(\$A\$1,ROUNDUP(ROWS(\$1:1)
/COLUMNS(\$A\$1:\$D\$100),0)-1,MOD(ROWS(\$1:1)-1,COLUMNS(\$A\$1:\$D\$100))))

Sun 03 Jan 2016

Challenge 33 – Convert Matrix into Linear Column – II

By |Sunday, January 03rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Last challenge, we converted a matrix into Linear Column. Last time, we picked up values in a row and populated the column and then moved to next row. This time, we have to do exchange row with column. Hence, you need to pick up values from one column and move to the next column and so on.

Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between.

The column ranges would be A to E whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider A1:E100 range. (Note - There would be no blanks in between the values in the Grid)

Download the workbook related to this from Challenge 33 - Convert Matrix into Linear Column - II

The solution to this challenge will be published after a month i.e. on 2-Feb-16.