+ Reply to Thread
Results 1 to 4 of 4

Trying to use conditional formatting to color cells that match list on other spreadsheet

Hybrid View

  1. #1
    Registered User
    Join Date
    01-26-2021
    Location
    New Jersey, USA
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Trying to use conditional formatting to color cells that match list on other spreadsheet

    I'm trying to figure out how to get this spreadsheet to highlight drugs on an invoice that match a certain list. I need each class of medication to highlight differently on the main sheet, ex: blood drugs have a red background, chemo have a purple, etc.

    To better explain, the first sheet is called "Paste Invoice Here". The user will download invoices as a CSV and paste the entire invoice in field A1. I want fields J17-J82 to look for matches on the sheet "Sorting Data", and highlight with the appropriate color. The drugs have different forms, which is why I have asterisks on the words. Basically I want "Paste Invoice Here" to reflect the color on "sorting data" so she can see which of each class of med is on the invoice after she pasts it, if this is applicable. If not my goal is to move the information to that cell into a list below each class of medication on the "results" page.

    I have a copy of my excel attached, and thank you so much everyone for the help on this site
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-26-2021
    Location
    New Jersey, USA
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: Trying to use conditional formatting to color cells that match list on other spreadshe

    I have a few images attached below to better explain. The image with the 6 lists shows boxes around the medications I manually highlighted in the invoice. Is this possible to do automatically with a formula?
    Attachment 715764
    Attachment 715763

  3. #3
    Registered User
    Join Date
    01-26-2021
    Location
    New Jersey, USA
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    5

    Re: Trying to use conditional formatting to color cells that match list on other spreadshe

    Trying to attach the list again due to an upload error.
    2.png

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,732

    Re: Trying to use conditional formatting to color cells that match list on other spreadshe

    In the attached file new lists are produced for each category without the asterisks in columns I:O on the Sorting Data sheet.
    Each list is also a named range with the exception of New Form which has no formatting.
    A helper column (S) has been added to the Paste Invoice Here sheet.
    The helper column may be moved and/or hidden for aesthetic purposes and is populated using:
    Formula: copy to clipboard
    =IFERROR(INDEX('Sorting Data'!I$1:O$1,AGGREGATE(15,6,(COLUMN('Sorting Data'!I$1:O$1)-COLUMN('Sorting Data'!H$1))/('Sorting Data'!I$2:O$86=J17),1)),"")

    Column J on the Paste Invoice Here sheet is conditionally formatted using rules like: =COUNTIFS(Non_Form,J17)
    The lists of medications on the Results sheet are populated using:
    Formula: copy to clipboard
    =IFERROR(INDEX('Paste Invoice Here'!$J$17:$J$82,AGGREGATE(15,6,(ROW('Paste Invoice Here'!$J$17:$J$82)-ROW('Paste Invoice Here'!$J$16))/('Paste Invoice Here'!$S$17:$S$82=B$1),ROWS($A$1:$A1))),"")

    The Total Cost is populated using: =SUMIFS('Paste Invoice Here'!$P17:$P82,'Paste Invoice Here'!$S17:$S82,B1)
    The Total Count is populated using: =COUNTIFS('Paste Invoice Here'!$S17:$S82,B1)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Drop Down List Color Selector - NOT Conditional Formatting
    By airj1012 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-08-2020, 05:32 PM
  2. Replies: 2
    Last Post: 04-08-2020, 05:30 PM
  3. Conditional Formatting - Color Formatting for Blank Cells
    By mlbdc2012 in forum Excel General
    Replies: 10
    Last Post: 02-10-2015, 06:28 AM
  4. Replies: 3
    Last Post: 05-30-2014, 10:24 AM
  5. [SOLVED] Conditional formatting color 3 numbers in another list
    By Berna11 in forum Excel General
    Replies: 5
    Last Post: 03-08-2013, 12:19 PM
  6. Pattern Search Of Conditional Formatting Color in Spreadsheet
    By ssjagger in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2013, 12:38 PM
  7. Conditional formatting: No background color when list item is selected
    By earthtodan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2008, 09:18 PM

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