1

Looked through available options, but failed to find the one in a format I would understand steps.

Working with Excel. Need to have cell changing color based on specific wordS (not one). For example if in cell someone inputs words "Mouse, keyboard, hub, external drive, pointer" plus a hundred of others from specific predefined (by myself in another hidden worksheet) list, then it changes in purple, and if words appear like "Excel, PowerPoint, Software, Array, Screen, Window" etc, it changes yellow. Other words, these words should attract attention of specific specialist owing to see the specific color (and words tagged to this color).

In a simple way:

  1. Create list A of 50 (or even more) texts (Worksheet 2, hidden)
  2. Create list B of 100 texts (or even more) (Worksheet 2, hidden)
  3. If in specific cell (column or row) of Worksheet 1 a text from List A appears, that specific cell (cells) change color to purple, if from list B then yellow.
  4. Cell remains unchanged if a) word is different than List A and B; b) misspelled or b) left blank.

PS:

  1. Do not want to use "Dropdown" option, considering that number of options could be huge and customers suffer of not seeing small text or being just lazy.
  2. Do want to go through "Conditional formatting" with "Specific text" for 2 reasons: 1) too long selecting it one by one and 2) would be difficult for auditor to check which text values are listed for format change.
|improve this question
  • The condition is a hit on any single word from the list (vs. need to match N words in the list)? Will the cell text be a single word that you need to lookup, or more like a sentence, where you have to see if any one or more words in the sentence match anything in the list? Is there any possibility that the cell text could contain a match in both lists (if so, how handle it)? – fixer1234 Oct 15 '15 at 4:55
  • Not sentence, but multiple single words. Like a questionnaire from which each engineer should look up for (and act) based on their area. Specifically Hardware engineers should see Hardware tags (purple) while Soft should see soft tags (yellow). – Rovshan Guliyev Oct 15 '15 at 5:26
  • Is it all or nothing in terms of words in a cell being in one of the lists? For example, if a cell matches a list, should you be able to parse the first word, look it up, and be guaranteed of a match (in the right list), or would only some of the words be in the list, so you need to parse and check each word until you either find a match in the list or run out of words without finding a match? – fixer1234 Oct 15 '15 at 5:48
  • Any cell in Excel. Someone inputs any word. Excel checks it with Tag list and if match, then change color. If word is matched with Tag list 1 - purple, if with Tag list 2 - purple. Tag list (s) is (are) prepared and saved in the same Excel document in back-up (hidden) worksheet . Tag list is hidden and could be seen/ unhidden /edited by author only. – Rovshan Guliyev Oct 15 '15 at 5:57
0

What your basically asking is to do conditional formatting with reference to another sheet. But unfortunately excel cannot do conditional formatting with reference to another sheet. But you can do alternate method of mirroring as below:

To apply conditional formatting to Sheet1 using values from Sheet2, you need to mirror the values into Sheet1.

Creating a mirror of Sheet2, column B in Sheet 1

  1. Go to Sheet1.
  2. Insert a new column by right-clicking column A's header and selecting "Insert".

  3. Enter the following formula into A1:

    =IF(ISBLANK(Sheet2!B1),"",Sheet2!B1)

  4. Copy A1 by right-clicking it and selecting "Copy".
  5. Paste the formula into column A by right-clicking its header and selecting "Paste".

Sheet1, column A should now exactly mirror the values in Sheet2, column B.

(Note: if you don't like it in column A, it works just as well to have it in column Z or anywhere else.)

Applying the conditional formatting

  1. Stay on Sheet1.
  2. Select column B by left-clicking its header.
  3. Select the menu item Format > Conditional Formatting...

  4. Change Condition 1 to "Formula is" and enter this formula:

    =MATCH(B1,$A:$A,0)

  5. Click the Format... button and select a purple background.

You should now see the purple background applied to the matching cells in Sheet1.

Hiding the mirror column

  1. Stay on Sheet1.
  2. Right-click the header on column A and select "Hide".

This should automatically update Sheet1 whenever anything in Sheet2 is changed.

Use the above method for the cells you wanted to highlight in Yellow

|improve this answer
  • Very impressive, thanks, but stumbled at level 3 of "Applying Conditional formatting" - didn't work. Steps taken are: Conditional formatting > Use a formula to determine which cell format (no "Condition 1") > Format values where this formula is true – Rovshan Guliyev Oct 15 '15 at 5:20
  • If this has answered your question please click the tick mark besides the answer @Rovshan Guliyev – Dragonborn Oct 15 '15 at 6:19
  • @Dragonborn your answer is clearly not yet THE answer for the question, please help it make working before asking for accepting it. – Máté Juhász Oct 15 '15 at 6:56
  • Huge thanks - working. Though it created new question, but my initial request is settled. – Rovshan Guliyev Oct 15 '15 at 6:57
  • Any model a cell can combine 2 methods above: cell change purple if one word inserted and yellow if another - again from pre-defined list? – Rovshan Guliyev Oct 15 '15 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.