Google Sheet Circular Dependency Issue With If Function

Question

Am using if function in Column A of the Google Sheets

=ARRAYFORMULA(if( isblank(D3:D) , A2:A , D3:D ))

but getting Circular dependency error. If I use the same function without ARRAYFORMULA i.e.

if( isblank(D3) , A2 , D3 ))))

and then drag the formula, there is no error and the result is reflected.

Any help on how to fix the Circular dependency error with ARRAYFORMULA in above case would be greatly appreciated.

Answer 1

When you see the circular dependency detected error displayed in your Google spreadsheet, this means that your formula is referring to a range that contains the formula itself, or in other words when the formula input, is dependent on the output.

What you can try to do is turning on Iterative Calculation under File -> Settings:

If that fixes your issue then good. But based on your formula, I don't think this is a fix. The above option is best on recalculating values repetitively. Your current formula is not doing that.

If that's the case, then you'll just have to put the formula on another column to avoid the circular dependency.

I recommend you try and see the reference below about circular dependency and it might help you decide what you should do.

Reference:

• Circular Dependency