AUTOMATED Color Codes For Lookup Tables Using COLOR SCALE Conditional Formats

AUTOMATED Color Codes For Lookup Tables Using COLOR SCALE Conditional Formats

Simplest way;

  • add the RowID column (column type = Properties → Row ID
  • this yields a unique number for each row you add to the table
  • set the conditional format on this column to ‘COLOR RANGE’ and APPLY TO = All Columns
  • this results in each row getting a unique color AUTOMATICALLY
  • but deleting rows will result in gaps in the number sequence which eventually degrades the color range
  • so only use this if you dont expect lots of deletions and insertions over time

Better way;

  • add a numeric column with the formula: Find(Item, Table.Item)
  • this also generates a sequence number for each record
  • then set the conditional format on that numeric column to ‘COLOR RANGE’ and APPLY TO = All Columns
  • this method always numbers each row correctly, even if you delete and insert lots of times

These colors then get copied over to any LOOKUPs you use (and kanban boards etc)
Experiments · COLORS 10_08_2021 16_42_19

Thanks to the Codians who added the color range formatting feature - its great!

Respect
Max Xyzor

4 Likes

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

1 Like

ive just seen an alternative formula to generate the number field required…

thisTable.Find(thisRow)

which works similar to the one i used in the post above, but is more succinct

respect
max