I have a spreadsheet of text data; each column has a limited allowable set of string values. I have a second sheet in the file which, for each column in the first sheet, contains each allowed value exactly once. (There is currently no formula enforcing this; I am pretty sure that if I knew how to do that I wouldn't need to ask this question.)
What I want to do is make it so that each value in the main sheet is color-coded such that each allowed value for a given column has a different color. Manually assigning colors for each value is infeasible. I don't care about overlaps in color-range for separate columns.
I have seen this question, which is basically the same as my question but for Excel, and unclear in answer besides. Prompted by that, I've added additional columns such that to the left of each column in the domain sheet is a column mapping those values to consecutive integers. I don't know how to turn those into colors on the main sheet, though.
I did find instructions for color-scale formatting. This is about half of what I need; unfortunately, it seems that formulas can only be used there to define minimum, maximum, and midpoint values for the gradient, but not to process the cell value from a string to an integer to make it gradient-able.
Using that, I've gotten most of the way there: A third sheet reads the values from the first sheet, maps them to numbers using the second sheet, and applies a color scale to the result. This results in a sheet with the same data as the original, but represented in numbers and colors instead of strings. The remaining step (if this method goes anywhere) would be to either duplicate those colors onto the original sheet or have the new sheet display strings to the user while containing numbers for the formatter.
No comments:
Post a Comment