Tags: MACRO

Performing a text to rows operation

{19 Comments}

Given multiple text values in a cell which are separated by a semi-colon, one may want to split data into multiple rows along with existing data in other columns.  This is something similar to Data > Text to columns - the only difference being that data has to be split by rows instead of columns.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

You may watch a short video of my solution here

Solution for all versions of MS Excel

I have also shown a macro based solution to this problem.

You may refer to my solution in this workbook.

For accomplishing just the reverse i.e. merging data from multiple cells into a single cell (separated by a comma), you may refer to my solution at this link.

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 the this workbook, i present the following three cases of dealing with the secondary validation cell value when the primary validation cell changes:

1. The moment the primary validation entry changes, blank out the secondary validation cell
2. The moment the primary validation entry changes, show the corresponding first entry of the secondary validation drop down
3. The moment the primary validation entry changes, perform the following:
a. If there is only one corresponding entry in the secondary validation drop down, then show that entry (no need to select); and
b. If there are multiple corresponding entries in the secondary validation drop down, then blank out the secondary validation cell

Create a log sheet of all array formulas in a workbook

{0 Comments}

There may be many array formulas in a workbook.  One may want to create a two column table in a sheet named "Array formula log" which lists down sheet names and cell address of all array formulas.  Furthermore, the cell references listed on the "Array formula log" sheet should be hyperlinked.

You may refer to my solution in this workbook.

Automatically change validated entries when source of validation list changes

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