Tags: ADDRESS

Generate a list of assignees for different projects based on a competency matrix

{ 4 Comments }

Here is a small sample of a Project matrix which shows tasks to be accomplished for various projects.  There can only be upto 6 tasks per project. Project Name Task1 Task2 Task3 Task4 Task5 Task6 Project1 Painting Chef Gardener Project2 Tiling Digging Engineering Project3 Mechanic Engineering Here is a competency matrix showing the competencies of employees on […]

Read More →

Compute potential Sales of a retail outlet

{ 8 Comments }

The objective is to assist a Store Manager with computing potential sales across different products and colours.  To start with let’s assume two datasets: 1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and 2. Colour-Product-Price dataset – a three column table which lists down the multiple […]

Read More →

Perform an iterative sum of Top n values across multiple columns

{ 4 Comments }

A tournament has 18 participating teams with 25 players in each team.  Each team has to play five rounds of the Tournament and not all players play all rounds.  Scores earned by each player in each round are shown in individual cells.  If a player does not play a round, that cell is left empty. The task […]

Read More →

Show granular as well as total figures on the Summary sheet

{ 0 Comments }

Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions.  Each Region has its own worksheet.  There are three headings on each sheet – Particulars, 2009 and 2010.  While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]

Read More →

Create charts on different sheets by clicking a button

{ 6 Comments }

Assume a five column database with the first column containing codes, second one containing a separator, third one containing some alpha values, fourth one is a combination of the first three columns and the last column has response times. One may want to create as many charts as there are codes in column A.  The charts […]

Read More →

Summarise data from multiple sheets with one condition – PartII

{ 2 Comments }

Assume a worksheet which has scores (1-10) on four future dates on nine questions.  Each row represents responses for one question.  Likewise there are 20 respondents i.e. 20 worksheets which have the same structure. One may want to summarise data from all 20 worksheets into one worksheet.  The question and solution are more clearly explained in the attached […]

Read More →

Summarise data from multiple sheets with multiple conditions

{ 2 Comments }

Assume there are three worksheets, one each for 2008, 2009 and 2010.  On each sheet there are two columns for Account code and Account description.  Thereafter there are 12 columns (one for each month).  Values inside the matrix represent amount spent on a specific item in a specific month. On the P&L worksheet, a user will provide the […]

Read More →

Summarise data from multiple sheets with one condition

{ 6 Comments }

Assume there are ten worksheet tabs – one for each retail store.  Each worksheet tab has sale data of different brands across 12 months – brands are arranged in rows and months are arranged in columns.  The structure of all worksheets tabs is the same.  Furthermore, each store is managed in any of the two retail […]

Read More →

Summarise data from multiple worksheets

{ 0 Comments }

Assume multiple worksheets in a workbook (all having the same structure).  In a summary sheet, one may want to tabulate data from these multiple worksheets.  Essentially, a way to link to the same specific cells in multiple worksheets to the Summary sheet.  The formula in the Summary sheet should just be copied and pasted to multiple rows to bring […]

Read More →