Dec 09 2022 06:07 AM - edited Dec 09 2022 06:20 AM
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)
Dec 09 2022 06:33 AM - edited Dec 09 2022 06:38 AM
Solutionquick 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","")
Dec 09 2022 06:54 AM
Dec 09 2022 06:33 AM - edited Dec 09 2022 06:38 AM
Solutionquick 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","")