How to keep a list in order when drawing from a dynamic source

ShannaR17

Hi all,

I have a list of projects in a status update sheet. This list is automatically updated from a sheet that receives updates from a JIRA to Smartsheet connection.

My issue is that the list of projects is in a priority order (with a seperate column to give a priority number), but sometimes when the JIRA pull sheet is updated the list is thrown out of order.

Here is the formula I'm using to pull the list of projects in:

=IFERROR(INDEX(COLLECT({reference to list of project names}, {reference to project type}, "Epic Story", {reference to project label}, NOT(CONTAINS("Archived", @cell))), $[index helper list]$1), "")

How can I keep the projects in the order I set even when the list from the source changes?

James Keuning

Take a look at INDEX and SMALL formulas. If you have a helper column in your sheet called INCREMENT, you can set it to increment 1, 2, 3 etc. No gaps, just increment by 1.

So now when you use INDEX and SMALL, row 1, where INCREMENT=1, will go over to your list, look for the project with the lowest priority number, presumably 1, and put it in the first row.

Then the next row, where INCREMENT=2 will go look for the project with the second highest priority, and bring it into the second row.

In this method, if it does not find a priority 3, or 4, or whatever, it's fine, because it is not looking for that literal number, it is looking for the third highest, so if there is no 3 or 4, it will return 5.

So your sheet will have the INCREMENT field going from 1 to how every many projects you have, and a column formula that goes and grabs the smallest, then the next smallest, third smallest, etc.