IF/THEN Formula or Conditional Formatting

Pamela Wagner

I think ultimately I need a formula but will start to see if conditional formatting will work. We have a sheet where if a condition in a cell is true, then we want cells in that row to be shaded out. So if the Doc Type = Form, we want the 3 different quiz cells to be shaded grey and the rest of the cells to be left blank. I did not see a way to do this on individual cells in the row so then I turned to a formula. The formula is pretty simple to start:

=IF([Doc Type]@row = "Form", "N/A", 1)

So if the doc type is "form" then return an N/A to the cell, if not, return a checkmark.

But then I realized that there are other doc types and for those other doc types, in the cells that are text only (let's use "Quiz Name" as the example), it's leaving a 1 when what I would like is to leave the cell blank. I know this needs to be a nested IF statement or perhaps use an AND/OR within it but my brain is just not getting me where I need to be to do something larger than a simple IF statement.

Cam someone help?

KPH

Hi @Pamela Wagner

Part 1 - change 3 cells to grey if Doc Type = Form.

This can be done with conditional formatting.

Example:

Part 2 - Nested If

=IF([Doc Type]@row = "Form", "N/A", 1)

Will return "N/A" if True and 1 if not. If this is in a checkbox column, that will check the box.

I'm not sure what you want to change here. You can make the formula return blank instead of the 1 for other Doc Types by changing the 1 to "".

=IF([Doc Type]@row = "Form", "N/A", "")

So now, if it is a Form it will populate the cell with "N/A" and if it is another Doc Type, it will be blank.

If you want to nest and IF to do and IF/THEN type action, replace the "" with another IF. For example"

=IF([Doc Type]@row = "Form", "N/A",IF([Doc Type]@row = "Quiz", "Null", ""))

Now if will return "N/A" if the Doc Type is form, "Null" if it is quiz, and blank for any others.

I hope this helps.