AUTOMATED Color Codes For Lookup Tables Using COLOR SCALE Conditional Formats

Showcase Tips and Hacks
2

After using this approach for several documents, I have refined it a little…

  • I no longer use the coda RowID() function as it does not give the desired result if rows are deleted
  • Instead I set the row-number with Find(Column1, thisTable.Column1) - uses the order in the table
  • OR - I set the row-number with Rank(Column1, thisTable.Column1, ascending: true) - uses the sorted order for Column1

In each case, as the user adds or removes rows, the formula assigns a contiguous sequence of numbers, either based on their position in the table or their rank in the sorted order.

As before, I follow these steps…

  • Add a numeric column (eg; ‘row-number’) to store the number to use for the color range
  • Set its formula to Find(Column1, thisTable(Column1) to assign colors based on row position in table
  • Or use Rank(Column1, thisTable.Column1, ascending: true) for a sorted assignment
  • set a conditional-format on the row-number column,
  • choose the ‘uses color scale’ option
  • select the color scale I want to use
  • go to the ‘APPLY TO’ option and click either the ‘all columns’ option to color the whole row
  • or just ‘Column1’, to color just the Column1

IMPORTANT NOTE: THE “COLOR RANGE” OPTION IS NOT AVAILABLE NON-NUMERIC COLUMNS
So you have to create a row-number numeric column first, then use it for setting the color.

How the formula works…

  • we need a unique number per row to use for the ‘color range’ formatting
  • Find(myItem, listOfValues) returns a number indicating where myItem is in the listOfValues
  • this.TableColumn1 results in a LIST of all the values in Column1
  • so Find(Column1, thisTable.Column1) results in a number showing where Column1 occurs on the table - which is what we need
  • using the Rank() function instead of Find() does the same thing, but SORTS the list first

hope I am explaining this well enough for people to use it easily

respect
max

4 Likes