Forum Discussion
Solved
Conditional Formatting doesn't work with formula result
Hi all
I am having problems with conditional formating. What I want is to have 2 tabs showing number ranges in the form of the start and end of the sequence (i.e. 10 codes from 10990 to 10999). These codes can NOT be duplicated. Tab 1 is always in sequence from the previous range. But tab 2 is where the customer has chosen a specific range.
Right now the file works by using conditional formatting to highlight the lines on both tabs red when they show within the other tab. And that's fine. But for some reason it doesn't work when the range start/end is worked out through a shortening. An example is where a customer of ours puts a dash and an extra 0 into their codes but we have to reduce it to put it onto our system. e.g. 1099-00 would become 10990, and 1099-09 becomes 10999.
Despite the cells resulting in the same digits either way for some reason the second method stops conditional formatting from working. It's something I'm sure I can overcome with macros but I would always rather not use macros and I don't understand why the formula would matter if the answer is the same. PLEASE could somebody help with a solution to make this work? The formula to get the start/end numbers with the -0 removed is: =LEFT(E12,4)&RIGHT(E12,LEN(E12)-6)
1) I enter a number range in tab 1.2) I enter another number range in tab 2 which has all or some numbers already in tab 1. The row automatically highlights red to alert of the error.3) The corresponding row is also highlighted on tab 1.4) However there are times when the row is NOT highlighted in red despite showing in both tabs.5) Here you can see the digits in C and D are the same as before but now worked out translated from 1099-00 into 10990 and from 1099-09 into 10999. And this somehow stops the formatting from activating.6) Conditional formulas on tab 2.7) Conditional formulas on tab 1.
quick answer if I'm pretty sure your problem is the 'shortened' result is TEXT and not a number. Simply add = --( original formula ) to force excel to convert it into a number or use VALUE( orig formula). So in your example it would be:
=--(LEFT(E12,4)&RIGHT(E12,LEN(E12)-6))
FYI - another thought (not related to the actual problem) is you could use SUBSTITUTE() to replace "-0" with "" to remove those 2 characters instead of getting LEFT and RIGHT and putting them back together so again in your example it would be:=--SUBSTITUTE(E12,"-0","")
quick answer if I'm pretty sure your problem is the 'shortened' result is TEXT and not a number. Simply add = --( original formula ) to force excel to convert it into a number or use VALUE( orig formula). So in your example it would be:
=--(LEFT(E12,4)&RIGHT(E12,LEN(E12)-6))
FYI - another thought (not related to the actual problem) is you could use SUBSTITUTE() to replace "-0" with "" to remove those 2 characters instead of getting LEFT and RIGHT and putting them back together so again in your example it would be:=--SUBSTITUTE(E12,"-0","")- my god I could kiss you. It works perfectly now. I knew it would be something daft I overlooked and I knew it was something to do with how I got those "shortened" results but I just couldnt see it.
Thank you this has saved my sanity
