Applying Dynamic Sort to a range containing formulas

The Dynamic Sort component is an extremely valuable add-on component that I use regularly on dashboard projects. One of the limitations of this particular component is that it only sorts data ranges that do not contain formulas. This goes back to the standard understanding that you can not use a component to insert into a cell containing a formula. Until we see another version of Dynamic Sort that will enable data insertion to alleviate this problem, here is a simple work around…

UPDATE- Feb. 2010- Since this article was written, Dynamic Sort 2.0 now features multi-column sorting and the flexibility to sort in place or into a destination range. This workaround is no longer necessary.

In this sample, I modified one of the templates off of the website. When in Xcelsius, I modified the data by including a simple formula that summed values from two adjacent columns. By design, Dynamic Sort will not sort ranges that include formulas.

Download Source Files
*This source file requires the Dynamic Sort component- Click here to view information and get a free unlimited use trial.

To work around this limitation, you can use a transparent label-based menu configured with insert filtered rows. The trick to moving the entire range of data with insert filtered rows, is to add an additional column as shown below highlighted in gray.

The goal is to reference the entire range (with the formulas) as source data, then insert into a new range. This new destination range will be sorted, and then linked to our visualization components.

Hopefully as you look to sort data within your dashboard this component will help you fill in this functionality. I have submitted the enhancement request to ensure that this is built into the next version of dynamic sort.


More posts by

Leave a Reply

All rights reserved Goodman Group LLC