Forum Discussion

Julia8516's avatar
Julia8516
Copper Contributor
Jul 24, 2023

Conditional Formatting

Hello,   I am trying to create a rule for a spreadsheet we are using to keep track of attendance for an upcoming event. Basically, we have 2 columns, Column D for "Yes" and Column E for "No".   I...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Jul 24, 2023

Julia8516 

1) Column F.

Select F2:F100 or as far down as you want.

F2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula    =OR(D2:E2<>"")

Click Format...
Activate the Number tab.
Select Custom in the list of categories.

Enter   ;;;   (three semicolons in a row) in the Type box. This will hide the contents.

Click OK, then click OK again.

 

2) Column G.

In G2, enter the formula     =IF(D2="Yes", F2, "")

Fill down.

Julia8516's avatar
Julia8516
Copper Contributor
Jul 24, 2023
Thank you! When I do the rule for Column F it deletes the email address but in the row below the one it should be deleting... so it's making the wrong email address go away
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 24, 2023

    Julia8516 

    When you create the rule, the formula should refer to the row of the active cell.

    I assumed that your selection would begin in F2, so the formula refers to D2:E2.

    If your selection begins in F1, the formula should refer to D1:E1 instead.

    • Julia8516's avatar
      Julia8516
      Copper Contributor
      Jul 27, 2023
      the second formula is not working. How do I get the email address in column C to copy and paste to column G when "yes" is marked in column D?
    • Julia8516's avatar
      Julia8516
      Copper Contributor
      Jul 24, 2023

      HansVogelaar that might be what I was doing wrong. Thank you!