Forum Discussion
conditional formatting- if, then
I am looking for a conditional formula where if cell A1 says "A/B" then all the selected cells that contain "D" and "C" will go blank. But if cell A1 says "All" then all the selected cells remain as they are.
Michelle_King So depends on what you mean by "go blank". Conditional formatting cannot change the value of a cell, it can only change the formatting. So you can make the text and background the same color and therefor it will appear blank. Assuming that is good:
first you need to define the 'applied to' range (lets assume D10:G100 ) and the cell you want to check (lets assume J2). Then highlight the applied to range and go into conditional formatting -> use custom formula. Then type:
=($J$2="A/B")*( (D10="C")+(D10="D") )
then set the formatting to have the same text color and background color.
so note how J2 is $J$2 that is because in ALL cases you want to check that specific cell. Next notice how the formula only checks D10 from the range because excel uses this formula to check the first upper left cell of the applied to range and then will move to each of the other cells and adjust the formula accordingly (i.e. D10 will go to D11, etc... but $J$2 will stay J2 because of those $).
IF the cells may have more than C or D in it you may use something like ISNUMBER(SEARCH("D",D10)) to find a "D" anywhere in that cell.
More likely you want to 'hide' any cell that doesn't have one of the selected letters so maybe:
=AND(ISERR(SEARCH(MID($J$2,SEQUENCE(LEN($J$2)),1),D10)))
then if a cell doesn't have at least 1 of the characters in J2 then it will "hide" it.
2 Replies
Michelle_King So depends on what you mean by "go blank". Conditional formatting cannot change the value of a cell, it can only change the formatting. So you can make the text and background the same color and therefor it will appear blank. Assuming that is good:
first you need to define the 'applied to' range (lets assume D10:G100 ) and the cell you want to check (lets assume J2). Then highlight the applied to range and go into conditional formatting -> use custom formula. Then type:
=($J$2="A/B")*( (D10="C")+(D10="D") )
then set the formatting to have the same text color and background color.
so note how J2 is $J$2 that is because in ALL cases you want to check that specific cell. Next notice how the formula only checks D10 from the range because excel uses this formula to check the first upper left cell of the applied to range and then will move to each of the other cells and adjust the formula accordingly (i.e. D10 will go to D11, etc... but $J$2 will stay J2 because of those $).
IF the cells may have more than C or D in it you may use something like ISNUMBER(SEARCH("D",D10)) to find a "D" anywhere in that cell.
More likely you want to 'hide' any cell that doesn't have one of the selected letters so maybe:
=AND(ISERR(SEARCH(MID($J$2,SEQUENCE(LEN($J$2)),1),D10)))
then if a cell doesn't have at least 1 of the characters in J2 then it will "hide" it.
- Thank you mtarler!
