flag out of range values based on set of criteria and reference range

[Excel_learner]

Hi,
Can someone please help me with a conditional formatting problem? I've been pulling my hair over this.
On tab 1, I have patient lab values and on second tab I have the reference range. Can a formula be develop to flag the outliners for specific test given the site & lab name match on both tab? Thank you.

[6StringJazzer]

I am sure this can be done but can you describe a little more how to read this data? For example, on "tab 1" which is the sheet Value, there are Subject Numbers and Patient IDs. The second tab "LNR" has Subject Number but that seems to match the Patient ID on the other page, not the Patient number. And the values on Value have some dates earlier than the dates on LNR.

So walk me through this. If I am looking at Patient 000418 tested at MDACC on 2/8/2016 and Albumin is 45.0, how do I look up the reference range for that number on the LNR sheet?

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[Excel_learner]

Hi Jeff, thank you for looking into this. Basically, I need some formulation to run through the test results in the [Value] tab, compared them with the range from the LNR based on matched criteria (site and lab name) and highlight the out of range values .
For example, patient 0450 (lab=MDACC and site = 0004) has albumin value of 34.0 in red text. Normal range for same lab and site is 35 - 47 ([LNR] columns E & F). How do I fomulate to highlight this value?
If 2 criteria is too complicated, is one criteria better to managed?
Please use the new attachment.

Thanks again.

[6StringJazzer]

Before I dive into this any further, I notice two things.

LNR has multiple entries for the same combination of Site and Lab. Why is that? In this case the entries are duplicates but can you have multiple entries with different numbers? If not, why not just have a single entry for each combination?

LNR has effective dates, but you don't mention this in your search criteria. Can effective dates be ignored? That is, if a patient test date is earlier than the effective date in LNR, what do you do?

[Excel_learner]

the spreadsheets are truncated of a huge data set. the site and lab combination should not have different numbers. They were just repeatedly entered for each patient at the same site. Don't worry about effective date. It's there to show when site use one lab and switch to another. Site & lab combination should be OK.

[6StringJazzer]

Conditional formatting does not allow references to other sheets in formulas, but it does allow named formulas. So I used named formulas for each column of the low and high values in LNR.

I also added a "helper" column in LNR to provide a key to combine the site and lab. It might be possible without it, but it makes things a lot easier, especially when doing CF.

[Excel_learner]

Oh gosh... I could go bald & wouldn't be able to come up with a formula like this. Although I don't understand what it does, i hope to be able to replicate it for other tests. These are not even a quarter of the stuff that I need to review. Thanks so much! You saved my hair ... & life :-)

[Excel_learner]

I know I just marked this as solved. Please forgive me for asking a bonus question if it's possible to highlight the lower value in green and higher in red? Thank you.