Circular Referance

CJU

Hi

I have a circular reference in some of my sheets but not other. How can I locate where this error is?

CJU

More detail:

Project Intake Sheet - Start Date -> Link to Start Date in Project Metadata

Project Metadata - Start Date -> Link to Start Date in Project Plan

Returns that there is a circular reference but I cannot see it.

Genevieve P.

Hi @CJU

Is it possible that either your Project Intake Sheet or your Project Metadata sheet pull information from your Project Plan sheet?

For example, if your Project Intake Sheet has a formula or cell link to the Project Plan, then this would be circular. Try hovering over the cells in each of your sheets to see where the grey arrows are coming from; this will tell you what sheets are looking at those cells, as well as the blue arrows telling you what sheet data is being pulled in from. (See: Inbound Cell Links)

Are you only using cell-links or do you use formulas, too?

If you're still unable to find the cause, it's difficult to diagnose the issue without seeing your sheet set up. It would be helpful to see screen captures of all three sheets, showing the error (but please block out sensitive data).

Cheers,

Genevieve

CJU

Hi Genevieve

I have tried to find the circular reference but have not been able to. The off thing is that the lines, with Blue Arrows, have worked, but the blank one does not.

The line without Blue Arrow, I had just entered the information manually.

The Blue Arrow points to the Project Metadata sheet for the respective project.

I think the error is that the pulling in data and output both go to the Project Metadata sheet but different cells.

Genevieve P.

Hi @CJU

Yes, it will have something to do with the grey arrows - where you're sending the information to and then where you're pulling the information in from.

Even if the data is sending out to different cells than it's pull from, is it possible that the other cells (where you're pull IN from) are looking at those cells?

For example:

• Send info to Sheet 1 Column 1
• Sheet 1 Column 2 looks at Column 1
• Sheet 2 looks at Sheet 1 Column 2

This would then be circular. Do you have any formulas in the other sheet?

mobrien

OP - were you ever able to solve this? I have almost the exact same issue with the same sheet.

What I can find is that in the "Project Metadata" file, the first few columns do a lookup something like this: "=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Name]$1, {Portfolio Summary Header Row}, 0))"

(this particular one is the "Project Name" column)

You can see that it refers to the "Portfolio Summary Data" (technically the "Project Intake Sheet") sheet. Then, the "Portfolio Summary Data" sheet refers back to the "Project Metadata".

Actually, while typing this answer, after a week of trying to figure it out, I figured it out. For some reason the "{Portfolio Summary Data} range was getting confused by some of the columns in the report. I simply limited the range to only the columns needed and it started working.

This must a bug of some sort, since it only affected about 10% of rows (the rest were able to lookup without throwing the error). I could see how this could be considered a circular reference, but the columns that would have been "circular" would never be selected by this lookup because they are not named the same as the column that is being looked up. However, why the software only chose a handful to throw the error on, while they were all identical, is the interesting part.

TL;DR - change the scope of lookup ranges in formulas to only include needed columns (rather than the default setup of the "Project Metadata" sheet) so that it cannot possibly try to select a column that would create a circular reference, even though it is impossible by the hardcoded lookup field value.

Hope this helps someone.

CJU

Thanks

I will take a look again, I never did resolve it. Similar you you, it only occurs on some of the rows.