Error message when invoking Dynamic Filters

Mar 2, 2009 at 11:33 PM
When I click the Dynamic Filters button on the Document Actions chart, the filter dialog box frame is drawn and then the message below occurs. The "Reset All Filters" etc. buttons appear, but no sliders.

I'm running 1.0.1.76; clusters have been identified and metrics calculated.

I don't know if its salient, but the only significant change from standard is that I created a column in the Vertices sheet that uses vlookup() against the "Cluster Vertices" (slightly extended by adding a mirror column because the vlookup key has to be in the first column of the relevant range, not the second...) to label each vertex with the cluster it belongs to.  The idea was to then use this column plus dynamic filtering to look at individual clusters.

---------------------------
Microsoft NodeXL
---------------------------
An unexpected problem occurred.  If it occurs again, please copy the details to the clipboard by typing Ctrl-C, then post the details to http://www.codeplex.com/NodeXL/Thread/List.aspx.



Details:



[InvalidCastException]: Specified cast is not valid.



   at Microsoft.NodeXL.ExcelTemplate.DynamicFilterUtil.TryGetNumericRange(String sWorksheetName, ListColumn oColumn, Double& dMinimumCellValue, Double& dMaximumCellValue)

   at Microsoft.NodeXL.ExcelTemplate.DynamicFilterUtil.GetDynamicFilterParameters(Workbook workbook, String worksheetName, String tableName)

   at Microsoft.NodeXL.ExcelTemplate.DynamicFilterDialog.InitializeDynamicFiltersForOneTable(String sWorksheetName, String sTableName, GroupBox oGroupBox)

   at Microsoft.NodeXL.ExcelTemplate.DynamicFilterDialog.InitializeDynamicFilters()

   at Microsoft.NodeXL.ExcelTemplate.DynamicFilterDialog.OnLoad(EventArgs e)
---------------------------
OK   
---------------------------

Mar 3, 2009 at 1:00 AM
Pierre:

Does your VLOOKUP() function call return "#N/A" (Excel-speak for "value not found") for one or more rows?  I think that's what's causing the problem.  I'll fix this in the next release, but for now you should be able to work around it by modifying your formula to insert nothing into the cell when the lookup fails.  See "How to eliminate N/A error result when using Vlookup formula in Microsoft Excel":

http://exceltip.com/st/How_to_eliminate_N/A_error_result_when_using_Vlookup_formula_in_Microsoft_Excel/589.html

-- Tony
Mar 3, 2009 at 3:11 AM
Yes!  How ever did you know... That #N/A _was_ rather perplexing (still is, since there shouldn't be any given the data, AFAIK)

Super, thanks very much, Tony

Pierre
Mar 8, 2009 at 8:09 PM
This bug was fixed in the latest release:

http://nodexl.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=24375

The fix was to exclude from the Dynamic Filters dialog any column that has one or more #N/A values.  Why not just skip the #N/A value instead of excluding the entire column?  Because the Dynamic Filters dialog uses Excel's MIN() and MAX() functions to get the minimum and maximum cell values for each column, and MIN() and MAX() return #N/A when any cell value is #N/A!

-- Tony