Assume a column of data with blanks appearing in a few random cells. One may want to use this range of cells for creating a Validation drop down in a specified cell. After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well. The task is […]
Read More →
{ 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 […]
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 }
The objective is to determine the quantity of stock (of a particular type) to be transferred from “Stock surplus” locations to “Stock deficient” ones. If all the requirements cannot be met from one location, tap other locations. The final output should show the location from where stock is being transferred. Furthermore the order of determining […]
Read More →
{ 0 Comments }
Many a times writing nested IF() statements become cumbersome, lengthy and highly prone to errors. In worksheet named “Question”, one would like to avoid using spare columns for computation of landed value for the three product sources (“CKD”, “LMP”, “IMP”). While one can use a nested IF() statement, it would be unmanageable because of the length of the […]
Read More →
{ 0 Comments }
Given a certain beginning date and specified number of days, one may want to determine the ending date (beginning date plus number of days). When determining the ending date, the following two cases should be considered Case 1 Holidays are: 1. Sundays only; and 2. User specified public holidays Case 2 Holidays user specified public holidays […]
Read More →
{ 8 Comments }
Assume there are two columns with data repeated in all both columns. One may wan to extract the unique text values from a specific column based on conditions specified by end-user. 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 […]
Read More →
{ 4 Comments }
Given a single column with duplicates appearing, one may want to dynamically extract unique values to another range. 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 […]
Read More →
{ 325 Comments }
Assume a four column database with department as the second column. Entries in the department column may be repeated. A person may wish to create one worksheet tab for each department. Therefore, as and when a person names a worksheet tab as one of the departments, all rows of that department should appear on this new sheet. You […]
Read More →
{ 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 →
Blanks appearing in source data not to appear in Data validation list
{ 0 Comments }