+ Reply to Thread
Results 1 to 8 of 8

Highlighting a Cell if the text in a range matches the text in a separate workbook range

  1. #1
    Registered User
    Join Date
    12-02-2021
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    12

    Highlighting a Cell if the text in a range matches the text in a separate workbook range

    I apologize if this is in the wrong thread.

    I had originally posted a work book that I had names moved to a "Validated Trainer List" if they met a set of criteria. This "Validated Trainer List" is on the worksheet titled "Trainers" in the attached workbook titled "Test Workbook - Copy"

    I now have a separate workbook where managers list out their trainers for their teams. This workbook (attached) is titled "TEST Trainer By Shift List."

    What I would like to do is conditionally format the latter workbook so that if a name shows up on any worksheet of the workbook "Test Trainer By Sheet List" matches a name on the worksheet "Trainers" it will highlight the cell green. If there is no match it will highlight the name in red.

    Simply put if a name on the Trainer by Shift (shortened) workbook is a Validated Trainer they are marked green, and if they are not a validated trainer they are red.

    The workbook "TEST Trainer By Shift List" is appropriately color coded with the desired result.

    Thank you for any help!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    Re: Highlighting a Cell if the text in a range matches the text in a separate workbook ran

    Hi Trojan, I wrote up a quick solution on a couple of test workbooks to detail how this can work. Workbook 1 contains the full list, and workbook 2 has the names you want to highlight. I used numbers in my sample.
    On book 2 I use a simple lookup formula that if it finds a match will result in true, and combine that with an iferror formula that will result in false if a match is not found. I use a cell equals nothing so the blank lines won't display false. Once I have the results in C I simply use conditional formatting to display the colors in A. The formula column can be hidden or put out of the way. On this test, the formulas and formatting only go down to row 20.
    Cheers.
    Squeaky.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-02-2021
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    12

    Re: Highlighting a Cell if the text in a range matches the text in a separate workbook ran

    Seems to work great! I really appreciate your help!

  4. #4
    Registered User
    Join Date
    12-02-2021
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    12

    Re: Highlighting a Cell if the text in a range matches the text in a separate workbook ran

    Actually, it works well in the test workbooks i have set up, but when i added those formulas to the actual workbooks, it only seems to return a false value. I think because the List that is generated may not be recognized as text but is instead referencing the formula that is there pulling the name.

  5. #5
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    Re: Highlighting a Cell if the text in a range matches the text in a separate workbook ran

    Hi TJ, Working between workbooks is tricky at times. Since the formula in book 2 refers to another workbook the formula must have the reference to that workbook correctly. The easiest way to make sure is have both open, then select a blank cell on book 2. Under Home on the ribbon select AutoSum (far right), then select a cell on book 1 and press enter. In the cell on book 2 you will find the "path" from 1 workbook to the next in the brackets. If that does not help perhaps you can share the 2 workbooks you are using? Another test you can do is create the formula on a different sheet in the same workbook as book 1 to see if you can make it work there first. If so, the rest is syntax.
    Squeaky.
    Last edited by Squeaky; 02-28-2022 at 04:11 PM.

  6. #6
    Registered User
    Join Date
    12-02-2021
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    12

    Re: Highlighting a Cell if the text in a range matches the text in a separate workbook ran

    I've been playing with it more and I think it might be my companies internal settings.

    Both Workbooks are stored on Microsoft Sharepoint so all the managers can access it.

    I noticed that when it tries to update it gives a server error.

    Also, when i have the source data open in excel versus having it closed or just open on sharepoint the formula changes. Not sure if that impacts it. Im going to try and attach picturesAttachment 770540Attachment 770542Attachment 770543

  7. #7
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    Re: Highlighting a Cell if the text in a range matches the text in a separate workbook ran

    You will need to use the file attachment that is provided by the forum.

  8. #8
    Registered User
    Join Date
    12-02-2021
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    12

    Re: Highlighting a Cell if the text in a range matches the text in a separate workbook ran

    I reattached them

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2015, 09:47 AM
  2. Vlookup for text and add relevant values if text matches in the range
    By misys.til in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-19-2014, 06:11 AM
  3. Separate numbers from a varying range of text within a cell
    By RBEB in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2014, 11:43 AM
  4. Replies: 2
    Last Post: 08-05-2013, 07:32 PM
  5. If Cell Text Matches Any Text From Range, Then Apply VLOOKUP?
    By eliot1171 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 09:41 PM
  6. Formula to Find If Text In a Cell Matches Another Range of Cells
    By purplesamcat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2013, 07:45 PM
  7. Find the POSITION IN A RANGE of text in a string that matches value(s) in a range
    By Cornell1992 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2006, 03:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1