1

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.

Improve this question

1 Answer 1

Reset to default
1

There is no way to dynamically create a set of new colors except with a macro. It's possible to format using a color scale, as you stated. It's also possible to create any number of new colors with styles (described here), but the question states that there are too many different colors.

So instead, highlight any duplicates and any values which are not in the list.

  1. Select the cells to check for one column on the main sheet.
  2. Format -> Conditional Formatting -> Manage.
  3. Add, Condition 1 Cell value is, duplicate, Apply Style Bad. (This works in LO 5.4.3.2. If your version is not new enough, then use COUNTIF as described here).
  4. Add, Condition 2 Formula is, ISNA(VLOOKUP(A1;Sheet2.$A$1:$A$100;1;)), Apply Style Error (based on here).

For example, if sheet 2 contains the following list:

apple
banana
carrot
date
eggplant
fig
grape

Then on the main sheet, problematic values are highlighted.

highlited values on sheet 1

Note: You can enforce values to be in the list using Data -> Validity, Allow Cell range.

Improve this answer
4
  • This does not answer the question. I want categorical color coding, not membership tests.
    – Vivian
    Commented Mar 31, 2018 at 19:00
  • Then the only way is with a macro or by creating a different style for each color, as stated in the first paragraph of my answer.
    – Jim K
    Commented Mar 31, 2018 at 19:04
  • I've made enough progress (see edit) to be uncertain of that.
    – Vivian
    Commented Mar 31, 2018 at 19:09
  • Maybe I don't understand what you're trying to accomplish, but if you figure it out, post it as an answer.
    – Jim K
    Commented Mar 31, 2018 at 19:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .