Tags: NAMED RANGES

Create all possible combinations from different ranges without using VBA

{ 122 Comments }

Assume numbers from 1 to 8 in range B3:B10 and numbers from 9 to 16 in range C3:C10.  The objective is to create all possible combinations in column E from these two ranges without using VBA (macros).  This combined range of all possible combinations is called a Cartesian product. You may refer to the following steps […]

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 →

Calculate a unique count with conditions in a Pivot Table

{ 12 Comments }

Assume a three column table arranged as follows: Circle, Date of Fault and ID.  Dates in the date range span one week – November 26, 2012 to December 2, 2012.  A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week.  Data for one week […]

Read More →

Create a Pivot Table from multiple worksheets in different workbooks

{ 67 Comments }

Assume two workbooks named Book1.xls and Book2.xls.  In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb.  In both the worksheets – Jan and Feb, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of […]

Read More →

Create a Pivot Table from multiple worksheets in the same workbook

{ 328 Comments }

Assume a workbook with two worksheets – Jan and Feb.  In both the worksheets, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of headings on both worksheets As you can observe in this workbook, all three aspects mentioned above […]

Read More →

Removing dependent validation list from cell for one case

{ 4 Comments }

In cell B6, a user can select one of the following entries from a validation drop down list – Reason1, Reason2 and Reason3.  If the user selects either Reason1 or Reason2, a dependent validation drop down lists should appear in cell C6.  However, if the user select Reason3 in cell B6, the dependent validation list from cell C6 should […]

Read More →

Display text entries in the data area of a pivot table

{ 60 Comments }

Assume a two column database of approximately 20,000 rows (say from A1:B20237).  The first column has vendor names and the second column has Part codes (alphanumeric string).  Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1.  One vendor supplies multiple parts and therefore there would be repetitions of vendor names […]

Read More →

Secondary validation cell entry to update when primary validation cell changes

{ 30 Comments }

Having created primary and secondary validation drop downs, the issue is that when a primary validation cell changes, the value in the secondary validation cell does not become blank (or change automatically to first entry of the secondary validation list).  Instead the secondary validation cell remains the same as what was already selected earlier. In […]

Read More →

Conditional data validation

{ 14 Comments }

Assume that in range A2:A11, cells have been validated to allow a person to enter Y or N.  In B2:B11, cells should be validated based on the entry selected by the end-user in range A2:A11.  So, if a user selected Y in cell A2, then a drop down should appear in cell B2, allowing him to […]

Read More →