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. […]
Read More →
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 →
Conditionally formatting duplicate values across all versions of MS Excel is quite simple. However, to colour different duplicate values in different colours, refer to this workbook.
Read More →
{ 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 […]
Read More →
{ 0 Comments }
If a certain cell entry is found in column C of the other eleven sheets, then colour the entry in red. Furthermore, a summary sheet should show all red coloured entries. You may refer to my solution in this workbook.
Read More →
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 […]
Read More →
{ 6 Comments }
Create a data validation list from a range which has data appearing in multiple rows. I have shown two cases – one using macros and other using formulas. The question and solution have been explained in this workbook.
Read More →
{ 0 Comments }
Based on information regarding starting date of tasks and periodicity, generate a schedule/time-table on another sheet. The question and solution have been elaborately explained in this workbook.
Read More →
{ 0 Comments }
Depending upon tolerance limit specified for every technical parameter, extract a report which lists down all those day wise parameters which breach tolerance limits. The question and solution have been elaborately explained in the workbook.
Read More →
{ 0 Comments }
Depending upon customer requirements specified for width, thickness and length os material, extract a report showing all records from the master stock-list which meets the requirements. The question and solution have been elaborately explained in this workbook.
Read More →
Performing a text to rows operation
{ 20 Comments }