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 →
Having created a graph from a certain source data, if columns get added later to the source, they do not automatically append to the graph. While this is easy enough to accomplish in Excel 2007 and higher versions, doing the same in Excel 2003 is a different procedure altogether. Instructions are very clearly mentioned in […]
Read More →
The VLOOKUP() function returns data from a lookup_array irrespective of the filter setting of the lookup_array. To make the lookup_array of the VLOOKUP() function work only on the visible cells of a filtered range, refer to this workbook.
Read More →
{ 2 Comments }
One may want to create interactive graphs by allowing users to select indicators and years to be plotted (via check boxes and scroll bars). I have presented three cases in this workbook.
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 →
{ 46 Comments }
A financial concept, Day Sales Outstanding (DSO), is a measure of the average number of days that a company takes to collect revenue after a sale has been made. For further clarification, read here. Herein, i have presented the solution for two different data layouts. Further, Layout 2 has two further divisions – one for computing […]
Read More →
The SUMPRODUCT() function works on all cells in the specified range – whether filtered or not. However, one may want to apply the same function on the visible cells of a filtered range. You may refer to my solution in this workbook.
Read More →
Blanks appearing in source data not to appear in Data validation list
{ 0 Comments }