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?
Bookmarks