User profile for user: dani524
dani524 Author
User level: Level 1
7 points

Comparing Column Contents on Different Sheets

Newb here!


I'm trying to set up a highlighting rule that will allow me to keep a list of 'do not continue' items on one table, and compare it against a running list of restock requests in another.


I have a highlight rule applied to my A Column of the requests table to make anything with text that is (Do Not Continue::A(Body)) bold and red. However, when practicing and adding fake items to both lists, the rule doesn't activate (e.g. having 'Asdf' on both lists) OR it highlights cells that don't relate (e.g. an item named Isabella while there's no Isabella on the do not continue list).


Where am I misstepping?


TIA!

MacBook Air 11″, OS X 10.11

Posted on Jun 27, 2023 2:11 PM

Reply
Question marked as Top-ranking reply
User profile for user: Badunit
Badunit
User level: Level 7
30,342 points

Posted on Jun 27, 2023 3:27 PM

You can compare the value in the highlighted cell to the value in another single cell but not to the values in an entire column or range of cells. Also, the highlighting rule cannot be a formula. If your highlighting rule is Text is "(Do Not Continue::A(Body))" then it is comparing your cell to the actual text "(Do Not Continue::A(Body))".


Create another column in your Requests table. In that column use a formula that looks up the value in the Do Not Continue table and, if present, puts it in the column. The highlighting rule will compare with that cell.



Formula in Requests::B2 =XLOOKUP(A2,Do Not Continue::A,Do Not Continue::A,"",0)

fill down to complete the column


The highlighting rule is shown for A2. To create it for the entire column at one time,

  1. Select A2:A10 (in this example)
  2. Start creating the rule
  3. In the box where you see B2 in the screenshot, click on the green oval (not present in the screenshot)
  4. Click cell B2
  5. Click on the green checkmark to accept it
  6. Finish making your highlighting rule. It will make rules for A2:A10, comparing them to B2:B10, respectively.


Hide column B



View in context
2 replies
Question marked as Top-ranking reply
User profile for user: Badunit
Badunit
User level: Level 7
30,342 points

Jun 27, 2023 3:27 PM in response to dani524

You can compare the value in the highlighted cell to the value in another single cell but not to the values in an entire column or range of cells. Also, the highlighting rule cannot be a formula. If your highlighting rule is Text is "(Do Not Continue::A(Body))" then it is comparing your cell to the actual text "(Do Not Continue::A(Body))".


Create another column in your Requests table. In that column use a formula that looks up the value in the Do Not Continue table and, if present, puts it in the column. The highlighting rule will compare with that cell.



Formula in Requests::B2 =XLOOKUP(A2,Do Not Continue::A,Do Not Continue::A,"",0)

fill down to complete the column


The highlighting rule is shown for A2. To create it for the entire column at one time,

  1. Select A2:A10 (in this example)
  2. Start creating the rule
  3. In the box where you see B2 in the screenshot, click on the green oval (not present in the screenshot)
  4. Click cell B2
  5. Click on the green checkmark to accept it
  6. Finish making your highlighting rule. It will make rules for A2:A10, comparing them to B2:B10, respectively.


Hide column B



Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Comparing Column Contents on Different Sheets

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up