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.

 

[dwgnome]

Hi,

Thanks for post and second follow-up using cleanString. I tried your formula in posting #9 and it appears to work with the exception of the second number. Posting #10 results gave the same result as #9. The second number result seems to include the hyphen making the value a negative. Wrapping the second formula in ABS(HiNum) seemed to have rid of the hyphen. The other problem, is that when I copy the conditional formatting to the next column, where just above the data sits a different number range to compare to, the conditional format formula does not change column references (ie. D14 to E14) its always comparing to the number range at D14. If I make the cell references relative instead of absolute in the defined name, the cell reference on it’s own appears to change randomly from the assigned cell. For instance, I input:

=FIND("~",SUBSTITUTE('OCT 09S'!D14,"""","~",1)&"---~") and when I check the name it looks like this:

=FIND("~",SUBSTITUTE('OCT 09S'!G23,"""","~",1)&"---~"). I’ve always encountered this quirky behavior in Excel 2003.

Portability of this conditional format is important if it is going to work at all.

When setting up the named ranges, I actually put the formulas in individual cells to test. A curious thing happens when just inserting the formulas into the cell, for the second number the formula result evaluates to #VALUE! instead of the number like in the first number. I suspect it is because it sees the hyphen and cannot see the second number as a number or value and remains text.

For example the number range in D14 on Sheet Oct 09S is: 0.28766" - 2.23289"

firstChr =FIND("~",SUBSTITUTE('OCT 09S'!$D$14,"""","~",1)&"---~")
firstSubstrings =MID('OCT 09S'!$D$14,firstChr-ROW(INDIRECT("1:20")),ROW(INDIRECT("1:20")))
LoNum =VALUE(VLOOKUP("z",IF(ISNUMBER(VALUE(firstSubstrings)),(firstSubstrings)),1))

secChr =FIND("~",SUBSTITUTE('OCT 09S'!$D$14,"""","~",2)&"---~")
secSubstrings =MID('OCT 09S'!$D$14,secChr-ROW(INDIRECT("1:20")),ROW(INDIRECT("1:20")))
HiNum =VALUE(VLOOKUP("z",IF(ISNUMBER(VALUE(secSubstrings)),(secSubstrings)),1))


When the formula from posting #9 is put in cells (Not the defined name) I get the following:

firstChr formula 8 Position of first non-numeric character (quotes)
firstSubstrings formula: 6 The number of digits to capture before the quotes
LoNum Formula 0.28766 The results - Correct
secChr formula 22 Position of second non-numeric character (quotes)
secSubstrings formula 9 The number of digits to capture before the quotes
HiNum formula #VALUE! The results – not quite there…


By the way when I simply input the cleanString formula into the cell the results do away with the hyphen but appends the char(34) and 99E+99 – results show as:

0.28766"2.23289" "99E+99"

I tried real hard to follow what each part is doing, but even a visit to http://www.mvps.org/dmcritchie/excel/strings.htm just could not seem to figure out what your code is doing. Could you see what might be causing the HiNum to report as #VALUE?

I believe the formula searches (Finds) the first quote, but do not understand why you have a tilde instead of a “. Do not know the significance of &”---~”. So that I may learn to resolve future changes, would you be able to explain what the code is doing?

 

[mikerickson]

The "-" issue is why the cleanString name was added.

The original problem was about extra spaces. it sounded like you had a CF formula that almost worked. Would removing the spaces with SUBSTITUTE(A1," ","") and using that original formula work for you.

 

[dwgnome]

Sorry, not sure which formula you are referring to. I tried the SUBSTITUTE formula to rid of the spaces, but my formula still does not know how many characters to move before picking up the numbers and ingnoring the quotes.

If you are referring to your first set of formulas, I would not know where to start. Like I said, the cleanString formula did not seem to help when I did my test case.

Thanks again for your assistance.

 

[mikerickson]

cleanString should remove "-" from the string, eliminating the negative value problem.
Also it puts the address in one place so that changing the relative/absolute referencing shoud be easy.

 

[dwgnome]

I will retest your cleanString formulas and report results.

Would it help to include a portion of the spreadsheet or in this case not necessary?

 

[mikerickson]

If it isn't too large, it wouldn't hurt
have you downloaded Jeanie's HTML maker

 

[dwgnome]

The spreadsheet is not large and I can certainly post later when I get home. I do not have permissions on company computer to install Excel Jeanie's HTML Maker. Perhaps I can try attaching an image in the meantime. I see that you have to have a public domain location for images for which I do not have any accounts. I will use Excel Jeanie to post.

I retried your formulas and it only works when the cell being referenced is absolute (ie., $D$14). Outside of defined range, the secNumber still shows as #VALUE! when input directly in a cell.

 

[dwgnome]

Here is a sample portion of the spreadsheet form for which the conditional format is to be applied. Again it works in column D when copied down, but when copied across, the conditional format does not adapt to the new range in E14, F14 etc.

The CF reads:

=AND(D16>0,OR(D16<firstNumber,D16>secNumber))

So since the number in D16 is 2.5, it is above the limit of 2.23456 and turns red. But the formula remains fixed in E16 and does not use the range 0.15 - 2.2 in E14.



Conditional Format

Excel Workbook
	B	C	D	E	F	G
8	Month:	October	*	*	*	*
9	*	*	Dry	Dry	Dry	Dry
10	Week	Day	Scrub. #1	Scrub. #2	Scrub. #3	Scrub. #4
11	*	*	A-216	A-217	A-218	A-219
12	Plating Tank No.	*	35	37	38	40
13	Source No.	*	S-16	S-17	S-18	S-19
14	Allowable *Range	*	0.23456" * * - * * *2.23456"	0.15" - 2.2"	0.4" - 2.4"	0.3" - 2.3"
15	Week #1	*	*	*	*	*
16	Thu	1	2.500	0.21	0.69	0.71
17	Fri	2	0.260	0.59	0.70	0.71
18	Sat	3	1.300	0.58	0.69	0.69
19	Sun	4	2.400	0.59	0.70	0.71
20	Mon	5	0.190	0.59	0.70	0.72
21	Tue	6	0.23451	0.59	0.70	0.71
22	Wed	7	2.237	0.58	0.70	0.71
23	Week #2	*	*	*	*	*
24	Thu	8	0.59	0.58	0.70	0.71
25	Fri	9	0.59	0.57	0.70	0.71
26	Sat	10	0.59	0.58	0.69	0.70
OCT 09S

Excel Workbook
	N	O	P	Q	R
14	cleanString:	*	0.23456"2.23456" "99E+99"	*	*
15	firstChr:	*	8	*	8
16	firstSubstrings:	*	6	*	6.00
17	firstNumber:	*	0.23456	*	0.23456
18	*	*	*	*	*
19	secChr:	*	16	*	*
20	secSubstrings:	*	6	*	*
21	secNumber:	*	#VALUE!	*	*
22	*	*	*	*	*
23	*	*	0.2346	*	*
24	*	*	2.2346	*	*
OCT 09S

Hopefully we can reconcile why the named range only works with absolute reference.

 

[mikerickson]

The addressing issue can be fixed by selecting a cell in column D and changing the address in the cleanString defintion to D$14

 

[dwgnome]

Yes!! That did it.

Thank you so much. If not too much more trouble could you provide answers to the following?

1.Wondering why does the formula in P21 result in #VALUE! but provides the correct number in P24 when the definition is called out?

2. What does the 99+99 in cleanString do?

Have a good day.