Sat 21 May 2016

Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Saturday, May 21st, 2016|Categories: Challenges|Tags: , , , , , , , , , , |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 -

1

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

1

 A typical answer would look like -

1

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