Error Circular Reference

Zurisadai Rodriguez

I'm trying to pull information into a row depending of the information in another column from the same sheet but in the same row. So I made this formula, when It is in one cell I don't have problems but when I paste the formula in other cells o make a column formula I have the message "#CIRCULAR REFERENCE".

 

 =IFERROR(IF([Group of projects]@row="Yes", IF(ISBLANK([Parent Job Number]@row), "", INDEX([Job Number]:[Job Number], MATCH([Parent Job Number]@row, [Job Number]:[Job Number], 0)))) ,"")

Genevieve P.

Hi @Zurisadai Rodriguez

It looks like you're pasting this formula into the "Job Number" column, which is referenced as a full column reference in your formula, which is why you're receiving the error (see: #CIRCULAR REFERENCE error.)

When you have a column reference that looks through the entire column, you can use this once in its own column as it will ignore its own cell. However as soon as you have more than one cell referencing itself you will get the circular reference error, as you've found. See the header Reference a Whole Column in this article: Create a Cell or Column Reference in a Formula

Try creating a helper column next to this Job Number and paste the formula in there!

Let me know if this works for you.

Cheers,

Genevieve