Conditional Formatting/COUNTIF help with multiple criteria

Let me start this off by saying I am not a professional, and am so uneducated that I don't even know that I have the right search terms (because I couldn't find an answer, I did try) or the best formula for this solution.

Okay, so. What I have is a multiple page spreadsheet; page one is "Active" (all clients; unique ID number, first and last names, and a column for each type of report), page 2 is "Pending," (clients ID#, name, and whichever specific provider a request has been sent out, and the date out) and page 3 is "Received" (all of the received reports from page 2, with added received and effective dates).

I have a number of conditional formatting rules in place, including one to make sure that reports are not duplicated using a helper column and COUNTIF on pages 2 and 3, and another COUNTIF to highlight the first names of clients on page 1 that have an entry on page 2 (using the unique ID# but highlighting the name cell).

What I'm trying to do - using similar logic to the second rule mentioned above - is create a rule where I can make the second name be highlighted on page 1 if there is an entry on page 3 AFTER a certain date, in a cell that I can change as I update the Active page by adding the information in the Received page.

I'm close, I think! I tried some other functions but ended up using a helper column (might as well) managed to figure out

Please Login or Register  to view this content.

Where P8 is the last updated date, L is my helper column (ID and date).... but I can only make it EQUAL to P8, not after. That works great, and I guess I could do it one day at a time, but that isn't optimal.

I know I am close, but I just can't figure out what I can do. I tried adding in a greater than sign but it kept giving me errors and damned it, I am stymied, and I JUST KNOW there is a solution somehow.

Hi and welcome to the forum. Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

You may need to include an INDIRECT() function somewhere but we'll know when we see the file.

Okay, I think I stripped out all the unnecessary clutter and have the referenced formatting still in place. Please let me know if there is anything more I can explain or add!! Thank you!


EDITED: Ugh, I got so consumed with fixing the formulas i forgot to do the second part. Basically, I want to have it work so that the rule that highlights the top last name here

ex1.png

Will highlight BOTH last names, as it is referencing the 12/20 cell, and i want to have it work for all AFTER (and/or including is fine) that date, instead of just the single date.

ex2.png

Thank you!

Hi, welcome to the forum

1st, any time you combine (not add/sum) 2 cells, they become text, and you cannot test for 1 text being < or > another text.
2nd, with CF, it only needs a TRUE(1) or FALSE (0) answer to trigger it. So instead of this (which isnt wrong)...
=COUNTIF(Pending!$B$2:$B$3,$C2)>0
you could just use...
=COUNTIF(Pending!$B$2:$B$3,$C2)
3rd, try this...
=COUNTIFS(Received!$B$2:$B$3,$C2,Received!$I$2:$I$3,"="&$P$8)

WOW thank you so much for the kind words. Even if I/we do hear it often (we dont, btw), it is always gratifying to hear it again.

For many of us here, helping people like yourself, is the main reason we keep coming back. We look forward to seeing you come back again soon.

The best way to learn excel, is to use excel - ID a problem, and see what you can do to overcome it. a few things to always remember/keep in mind...
1. Never be afraid to experiment!! The worst that can happen is you need to reload the file, the best that can happen is you learn something new.
2. Dont be afraid to ask for help here, even if you think the question is silly. We all had to start somewhere, and we have many many newbies on here. At least, we will hold your hand and guide you, at most, we will probably do it for you.

Yes, you can remove that helper - sometimes they can be a great help though.
COUNTIFS syntax is pretty much the same as COUNTIF, except you can add many more criteria/tests

Mick, thank you too, for the kind words, they are always appreciated!!