Tags: INDIRECT

Compute MODE of all numbers split across multiple worksheets

{ 10 Comments }

Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook.  The task is the compute the MODE of these numbers.  Mode is defined as the value which appears most frequently in a range of cells.  So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 – 3 appears maximum number of times in […]

Read More →

Summarise data from multiple sheets with multiple conditions – Part II

{ 64 Comments }

Assume date wise revenue data has been shown on two sheets – Sports wear and Fitness equipment (Data worksheets).  On each of these two worksheets, there are two columns – Date and Revenue.  On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions.  The […]

Read More →

Generate a list of missing invoice numbers

{ 2 Comments }

In a range of cells, assume there is a list of invoices raised during a certain period.  Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range.  So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507. Given […]

Read More →

LOOKUP unique data from multiple columns where search string appears multiple times

{ 2 Comments }

In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched.  Furthermore,  the VLOOKUP() function returns data from one column only. To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, […]

Read More →

Sum data from a particular cell of last n sheets only

{ 0 Comments }

A number is entered in cell A1 of every sheet in a multi sheet workbook.  The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets – x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed […]

Read More →

Create employee wise Effort Utilisation Report

{ 0 Comments }

Assume a “Labour utilisation” report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names).  Each employee can perform multiple tasks on the same day and same task on multiple days. Given a list of all employees, one may want to answer […]

Read More →

Summarise data from different cells of multiple worksheets

{ 12 Comments }

In each of the four worksheets (one for each week) of this workbook, there is a two column database showing Employee ID and Revenue earned by that employee in that week.  Not all employees appear on all sheets and there is no fixed cell reference for any employee on any sheet – therefore employee A001 can […]

Read More →

Extract specific number of characters from an alphanumeric string without breaking any word

{ 4 Comments }

When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell.  Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell.  Furthermore, when splitting data into multiple cells, it should […]

Read More →

Performing an iterative lookup to return closest match

{ 5 Comments }

Suppose a worksheet name is Code_details.  The following values lie in range A2:A14 of this sheet: A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123, ASDFG1234567890 and ASDFG123456789 Suppose there is another worksheet named Summary where the following values lie in range B5:B7: ASQ47809876,ASW675458 and QWERT Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet.  If this exact value is not found there, then the […]

Read More →

Analysing customer walkin data by date and service taken

{ 5 Comments }

Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data: 1. For every date, total number of customer walkins and […]

Read More →