Using Excel conditional formatting Icon Sets based on Specific word in other cell won't work?

khavusr

New Member
Joined
Mar 10, 2016
Messages
7
I have two sheets in a workbook, SheetA and SheetB, and I'm trying to make use of Excel conditional formatting icon sets to visualize the results better. So what I'm trying to do is that I'm checking a specific cell in Sheet A for a specific word and in my case it's the word "Yes" and the formula that I'm using in Sheet B to do so is the following; =IF(SheetA!D10="Yes","1","0"). Based on that if the word is actually Yes set the cell value to 1 and anything else will give the result 0. But, I noticed that I can't make use of conditional formatting icon set based feature due to that the cell contain a formula or at least this is what I'm believing. Since if I manually deleted the formula and enters 1 myself the conditional formatting works fine.

What is that I'm missing out here to get it to work?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
When entering the conditions, are you setting the " value " choice to the number 1, and the " type " over to the right as " Number "...

It should work....
 

khavusr

New Member
Joined
Mar 10, 2016
Messages
7
Yes, those options has been set. Also, if I remove the =IF formula from the specific cell and enters manually either 1 or 0 then the conditional formatting works as it should. But, as soon as I enters the formula, =IF(SheetA!D10="Yes","1","0"), the same specific cell then the conditional formatting stops working. I have also tried to use another column to make the formula check on the other sheet if it has something to do with that the conditional formatting doesn't work with certain formulas being used in a cell. But, that didn't make any difference. Is it to be concluded from this that conditional formatting doesn't work when using formulas?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,480
Office Version
  1. 365
Platform
  1. Windows
Hi, try removing the double quotes from the around the 1 and 0.

i.e.

=IF(SheetA!D10="Yes",1,0)
 

khavusr

New Member
Joined
Mar 10, 2016
Messages
7
Hmf! That did the trick and realize that with the quotes it's seen as text and not numbers.

Big Thanks!
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,480
Office Version
  1. 365
Platform
  1. Windows
Glad it worked! Oh, and welcome to the forum :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,185
Messages
5,623,260
Members
415,958
Latest member
pandagurl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top