AUTOMATED Color Codes For Lookup Tables Using COLOR SCALE Conditional Formats

[Agile_Dynamics]

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)

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

Respect
Max Xyzor

[Agile_Dynamics]

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

[Agile_Dynamics]

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

[John_Freeman]

Thanks for this! I wanted to have colors that wouldn’t change even after adding or deleting rows, which I achieved like this:

• Add a Number column of type Number. Edit its options. Set Value For New Rows to the formula Max(thisTable.Number)+1.
• Add a Color column of type Number. Set its value to the formula Number % 6.
• Add a conditional formatting rule for the Color column. Set it to use color scale. Change Min to 0 and Max to 5. Apply to All Columns.

If you want a different number of colors, change the modulus in the Color column formula and the Max value in its conditional format.

[Nate_Gerber2]

I used to do this all the time… but now I’m trying to find the color range setting in conditional format and don’t see it anywhere… Has this feature been removed? If so, any other solutions/workarounds for ergonomic colour management?

[Nate_Gerber2]

Edit - I just realized my mistake - forgot that I had to change the column type to number. My apologies!