+ Reply to Thread
Results 1 to 7 of 7

True/False for Multiple conditions and formats in a range of cells

  1. #1
    Registered User
    Join Date
    07-07-2015
    Location
    Jacksonville
    MS-Off Ver
    2013
    Posts
    4

    True/False for Multiple conditions and formats in a range of cells

    I may not be a n00b, but I haven't had success browsing the forums on this so far....

    I regularly generate an Excel report from our accounting software that lists all professional registrations for all employees in any jurisdiction. The report treats each license separately, so an employee licensed in 20 states will show up 20 times, thus it is large and unwieldy to read through.

    I would like to park a recent copy of this report on my server, along with a second "reader" spreadsheet, formatted as a matrix with US states in each row on the left, and types of professional registrations listed across the columns at the top, that will sort through the information in the report. The report displays information in columns shown below:
    • Valid License (This is represented by a simple Y or N)
    • US State the license is valid in
    • Professional designation of the license
    • Expiration date of the license

    I want each cell of the matrix to display a simple green/red background to indicate whether or not we have an employee with that registration in that state. Thus, if any employee has a valid license for the professional registration in that state, for which the expiration date has not passed, the cell displays a green background, otherwise it is red.

    I have tried combinations of COUNTIFS, AND, IF formulas, and nothing seems to work right. Any suggestions, or do I need to start learning VBA?
    Last edited by Vatoemperor; 07-07-2015 at 02:09 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: True/False for Multiple conditions and formats in a range of cells

    Can you post a small file showing results (conditions).

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: True/False for Multiple conditions and formats in a range of cells

    Welcome to the forum

    I would suggest hat you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    07-07-2015
    Location
    Jacksonville
    MS-Off Ver
    2013
    Posts
    4

    Re: True/False for Multiple conditions and formats in a range of cells

    MATRIX.xlsxLicenses.xlsx

    The matrix is a reduced version of the format I am looking for. I would have either a green/red or Y/N populate each cell corresponding to the license and the state. The licenses file is a condensed version of the data I am looking to aggregate.

    I basically need to know, for each state, if any single row satisfies ALL the other conditions shown.

    The only results I was getting were incorrect TRUE/FALSE and #VALUE, I think I need to start form scratch.

  5. #5
    Registered User
    Join Date
    07-07-2015
    Location
    Jacksonville
    MS-Off Ver
    2013
    Posts
    4

    Re: True/False for Multiple conditions and formats in a range of cells

    Either of these of any use?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: True/False for Multiple conditions and formats in a range of cells

    I combined your files into 1 file, with 2 sheets, then used this....
    =IF(ISNUMBER(MATCH($B5&C$4,INDEX(Sheet2!$F$4:$F$23&Sheet2!$E$4:$E$23,0),0)),"Y","N")
    copied down and across
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-07-2015
    Location
    Jacksonville
    MS-Off Ver
    2013
    Posts
    4

    Re: True/False for Multiple conditions and formats in a range of cells

    Awesome! That works exactly as I was looking for, hadn't even considered an INDEX...

+ 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: 2
    Last Post: 05-26-2015, 07:29 PM
  2. What is best to use for helper cells 0 or 1, typed true or false or =true =false
    By Gingermuppet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2014, 10:41 PM
  3. Multiple True or False + Sum of corresponding cells
    By CheezZZ in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-04-2013, 10:18 AM
  4. Replies: 3
    Last Post: 05-29-2009, 05:42 AM
  5. Replies: 2
    Last Post: 05-29-2009, 04:35 AM

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