2

This is Excel 2016.

I am using a workbook to manage some personal workflow tracking and I have toggles that mark H column cells as "True", "False" or having a zero character string ("") and I can not get the cell formula to see "" as "" for the purposes of clearing out the working cell.

I am using this currently

=IF(H1=TRUE,"Tested",IF(H1=FALSE,"Not Tested",IF(H1="","")))

But it does not work.

If H = True it prints "Tested" to the cell containing the formula and turns the row Green using conditional formatting applied to the entire row enter image description here

If H = False it prints "Not Tested" to the cell containing the formula and turns the row Yellow using conditional formatting applied to the entire row enter image description here

but if H is blank or contains a zero character string it doesn't print a zero character string, or alter the contents of the cell containing the formula at all the cell remains containing whatever the last thing printed to it was. HOWEVER the conditional formatting applied to the entire row does still turn it blue and the reference cell is the rows H column cell and the reference it checks is for "". So it is clearly seeing "" in that cell otherwise the conditional formatting wouldn't apply. enter image description here

Given that I use the exact same cell state detection to set conditional formatting for the cell and with the conditional formatting formula it DOES see H as a zero character string I know Excel is recognizing the H as having a zero character string.

=$H1=""

This is what I use to change the row-in-question's formatting for each of the 3 states and you can see in the pictures that is is clearly functioning.

This does apply the conditional formatting to the desired cell when I use the toggle to print a zero character string to H so I know the toggle is actually printing a zero character string to H and Excel is seeing it as containing a zero character string so why doesn't my cell formula that relies on the exact same state also trigger?

0

1 Answer 1

2

When you are using a formula IF(H1=FALSE,[VALUE IF TRUE],[VALUE IF FALSE]) the formula checks for a FALSE or a blank cell in H1.

If you wish to retain your current requirements, put the if blank at the beginning.

=IF(IF(H1="","",IF(H1=TRUE,"Tested",IF(H1=FALSE,"Not Tested","ERROR")))

The "ERROR" bit will highlight an error in cell H1

5
  • 1
    Suggest ISBLANK(H1) in lieu of H1="".
    – Aaron
    Jul 20, 2021 at 6:08
  • @Aaron please see superuser.com/q/1663580/581166 Jul 20, 2021 at 6:23
  • 1
    Thankyou! That worked, I had to change it to =IF(H1="","",IF(H1=TRUE,"Tested",IF(H1=FALSE,"Not Tested","ERROR"))) but it works now Jul 20, 2021 at 8:47
  • @BrandonHeath Ah yes, a missing close brackets at the end. We'll spotted. Edited now. If this answered your question, please accept the answer by clicking the tick 😀 Jul 20, 2021 at 10:16
  • Yeah, thank you, sorry I don't know much about StackExchange lol this is my first time. Jul 21, 2021 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.