Conditional Format Problem

[dwgnome]

Hi all,

Looking for help on a conditional format formula that is based on a text range within a cell. In my case, I would like to compare the low and high range when inputted as a number followed by the quotation marks followed by space then hyphen, space then number and ending in quotation marks. The quotation mark is being used to represent inches.

Like this in cell D14: 0.2” - 2.2”

I am using the following condition format formula which works for the strict case above.

=OR(D16<LEFT($D$14,3)+0,D16>MID($D$14,8,3)+0)

However, there will be times when users using this spreadsheet may leave out the spaces on either side of the hyphen, or add more than one space between the number and hyphen. Also if additional digits are added, my formula above does not include all the digits in the results. I prefer to leave the quotation marks in.

I was able to come up with a great solution using Laurent Longre's WMID from (Morefunc add-in). In testing within the worksheet, it worked, but when I attempted to place in the condition format, I got the warning message saying “You may not use references to other worksheets or workbooks for conditional formatting criteria.”

The formula that works is:

=OR(D16<WMID(D$14,1,1,"""")*1,D16>1*SUBSTITUTE(WMID(D$14,2,1,"-"),"""",0,1))

I then split the formula above into two named ranges:

HighS16 =1*SUBSTITUTE(WMID('OCT 09S'!D$14,2,1,"-"),"""",0,1)
LowS16 =WMID('OCT 09S'!D$14,1,1,"""")*1

And substituted back in the conditional format as:

=OR(D16<LowS16,D16>HighS16)

If expression is TRUE I have it set to highlight in red. But for some reason, the conditional format is not working.

Perhaps using named ranges would not be advantageous, since I have multiple columns with differing ranges. I would have to develop a separate named range for each column, making it harder to just drag and copy across and down.

Is there a work around to allow use of this formula or an equivalent non Morefunc formula that I can use?

Any assistance is appreciated.

 

[mikerickson]

1) I suspect that you did not press Ctrl-Shift-Enter (Cmd+Return for Mac) when entering P21.

2) the 99E+99 is a my attempt to give the formulation default values. AND to account for the possibility that the user will forget to put a " after the last number entered.
The cleanString that you're using will 99E+99 would cause a blank cell to be intepreted as 0" - 99E+99" i.e. all numbers fall into that range.
The formulation fails the conditions that I set for myself.

a) all numbers in the proper format (i.e. two numbers followed by ") should be interpreted as written (failure - negative numbers are not accepted in either position)

b) if only one number is entered (e.g. 23" ), secondNumber should return 99E+99 so that all numbers > 23 tested against the "interval" will count as OK.

c) the if the last of the entered values lacks a ", but ends in a number, the last entered number should be interpreted as if the " were there.
(e.g. 23" - 45 would return firstNumber=23 secondNumber = 45
12" returns firstNumber = 12 secondNumber = 99E+99

d) a blank cell should be interpreted as firstNumber = -99E+99 secondNumber = 99E+99 (i.e. testing any number against the range in a blank cell results in OK)

I've improved the formulation a little.
Name: firstChr
RefersTo: =FIND(CHAR(5),SUBSTITUTE(cleanString,"""",CHAR(5),1))

Name:firstSubstrings
RefersTo: =MID(cleanString, firstChr-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

Name: firstNumber
=VALUE(VLOOKUP("z", IF(ISNUMBER(VALUE(firstSubstrings)),(firstSubstrings)), 1))

This formulation of firstChr prevents the presence of ~ in the user entered string from interfereing.
The explicit arrays in firstSubstrings removes the volatile INDIRECT, making the spreadsheet faster.
This version of cleanString, meets some of my conditions, but playing with the three constant strings doesn't seem to be getting me closer to those conditions.

Name: cleanString
RefersTo: ="0"&SUBSTITUTE(SUBSTITUTE(Sheet1!$A1,"-","")," ","")&" 99E+99"&CHAR(34)&"99E+99"&CHAR(34)

 

[dwgnome]

For your first comment, yes you are correct, I did not CSE the formula, for I did not know it was an array. FirstNumber seems to work without CSE so did not think SecNumber needed it. It works now.

With regard to the use of INDIRECT, your idea using built in array 1 through 20 and the forethought of users forgetting to put in the second " is a good idea.

I appreciate the explanations and improvements you made, even challenging yourself on this really shows your dedication to applying Excel in everyday use and in helping others like myself who strive to also learn.

Still have trouble with the define - named - cleanString keeping the D$14 (non absolute reference) from "drifting" to other cells on its own. But for now it seemed to stabilize.

 

[mikerickson]

Named ranges have relative and absolute referencing, just as cell formulas.
Select the cell where you want the result to appear, and check the Name definition to make sure that the cell address, including $'s, is the way you would want it to be for a formula in a cell.

 

[dwgnome]

Will do. And thank you.