Author: Ashish Mathur

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 following five inputs - beginning month, beginning year, ending month, ending year and Account code.  Accordingly, the value should get summed up from multiple sheets.

You may refer to my solution in the this workbook.

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 formats - Joint venture or Owned and Operated (O&O) by the company.

One may want to summarise data from the ten worksheets tabs for each brand across 12 months for a certain retail format.

You may refer to my solution in this workbook.

Automatically change validated entries when source of validation list changes

{88 Comments}

After creating a validation list in a cell, one may enter one any value in that cell from the cell drop down.  Once a selection is made in the cell drop down, a change in existing source entries (say a spelling change) of the validation list will not cause the cell entry to be changed (although the cell drop down values change).

To change the cell value when the validation source entries change, refer to the this workbook.

Display auto filter criteria in a cell

{2 Comments}

Assume data in range D6:E11 - months in D6:D11 and numbers in E6:E11.  Headings are in D5:E5.  On filtering multiple values in column D, one may want to view the filtered criteria in cell (separate criteria by comma for multiple selections in auto filter drop down).

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.  This add in will enable us to use the following two functions

1. UNIQUEVALUES(); and
2. MCONCAT()

You may refer to my solution in this workbook.