Formula: Set blank cell text if another cell contains certain text

caseybateman

I have a sheet with 2 columns: Shift and Provider. If the Provider column is BLANK AND the Shift column contains "Primary", I want the Provider column to say "Coverage Needed", otherwise it should display whatever name is in Provider column.

I've played around with several variations of formulas but always receive a circular reference. I'd like it to look like below.

Paul Newcome

You are going to need a Change Cell automation instead of a formula.