Using Table Min/Max data in conditional formatting to flag if outside specified range

Please upload a representative Excel file and not a pic or screen shot. It saves having to retype data you clearly have already. You often get faster/more response if you do.

If you are unfamiliar with how to do this (the paperclip icon does not work):


To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

What do you want? Where do you want to see it? Some manually calculated results on your sheet would be helpful...

I think the OP wants to highlight (CF) any readings that are not within tolerance based on the minimum and maximum values in the lookup table.

Maybe. However, dedonx... you have used merged cells. There should NEVER be used in the body of your data. Make the columns wider. It's still do-able, but will look like a dog's breakfast as a result.

I have the CF formula... but can't apply it to your cell range. Never seen this behaviour before!!

=AND(OFFSET($E55,,INT(COLUMNS($A:A)-1),,)>=VLOOKUP(Sheet1!$A55,Sieve_Table,2,FALSE),OFFSET($E55,,INT(COLUMNS($A:A)-1),,)<=VLOOKUP(Sheet1!$A55,Sieve_Table,3,FALSE))

It must be something to do with those merged cells. I am not inclined to do any more on this. Either the merged cells go... or I do...

It wasn't the merged cells. It was the structured table. This works. In-spec values are shown in Green.

AND(OFFSET($E55,,INT(COLUMNS($A:A)-1),,)>=VLOOKUP(Sheet1!$A55,'Sieve Table'!$A$2:$C$25,2,FALSE),OFFSET($E55,,INT(COLUMNS($A:A)-1),,)<=VLOOKUP(Sheet1!$A55,'Sieve Table'!$A$2:$C$25,3,FALSE))

Good job, Glenn!

Improved version.

It seems that CF won't take structured table refs... unless you wrap them in INDIRECT. This now flags red those out of spec and green those in spec.

for in spec:A)-1),,)>=VLOOKUP($A55,INDIRECT("Sieve_Table"),2,FALSE),OFFSET($E55,,INT(COLUMNS($A:A)-1),,)<=VLOOKUP($A55,INDIRECT("Sieve_Table"),3,FALSE))

and for out of spec

=IF(ISBLANK(E55),FALSE,NOT(AND(OFFSET($E55,,INT(COLUMNS($A:A)-1),,)>=VLOOKUP($A55,INDIRECT("Sieve_Table"),2,FALSE),OFFSET($E55,,INT(COLUMNS($A:A)-1),,)<=VLOOKUP($A55,INDIRECT("Sieve_Table"),3,FALSE))))

So apart from merged cells, I have another thing to moan about - structured references.

So, just use the out of spec CF formula that Glenn has given you, not the in spec one.

There are no asterisks in your sample data, so yes, that may well make a, um, huge difference!!!

Delete the cf rule that shades green.

The other bit won't matter. If theres nothing in the meadurement cells, no formatting will be applied.

I am away now until uk morning time.

But.. you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Somehow or other you had it running from row 1048756!! CF formulae are sometimes a bit fragile and you should check them if they misbehave. Anyway, it's all fixed now.

Whenever you set ranges in CF rules, ALWAYS go back and double-check them. They do often change in this way, but once amended will stick. It's a frustration and must be an Excel bug. I see it often.

Originally Posted by AliGW

Whenever you set ranges in CF rules, ALWAYS go back and double-check them. They do often change in this way, but once amended will stick. It's a frustration and must be an Excel bug. I see it often.

I found that happened whenever I typed in or deleted the "$" manually. If I cycle through using F4 I've never had that happen again. Same is often true in the other managers ... Name, Data validation

Originally Posted by FlameRetired

I found that happened whenever I typed in or deleted the "$" manually. If I cycle through using F4 I've never had that happen again. Same is often true in the other managers ... Name, Data validation

Ah, interesting!