fixing circular reference

johnnipearson

Hi Everyone

Can anyone help me fix a circular reference with this formula. I am not sure how to fix this -

=INDEX({Calendar - Home Appointments 1}, MATCH([Date of Arrival (2)]@row, {Electrical Tasks}, 0))

thank you

KPH

Where are you placing the formula? Is it in any of the columns you are referencing? If not, can you share what the content of the cross sheet references are? A screen shot of each sheet with a key to say which column is the one you called {Calendar - Home Appointments 1} and which is the one you called {Electrical Tasks}, would be great.

johnnipearson

Hi KPH

Thanks for getting back to me and helping out. Its much appreciated! Please see the attached photos. I need "Calendar - Home Appointments 1" [Date (1)] to reference itself onto Electrical Tasks [Date of Arrival (2)].

=INDEX({Calendar - Home Appointments 1}, MATCH([Date of Arrival (2)]@row, {Electrical Tasks}, 0))

2 x SS Sheets.png

Thank you

KPH

It looks like you have the index formula in the column called Date of Arrival (2) in the Electrical Tasks sheet. And that formula is trying to match the value in Date of Arrival (2) in the Electrical sheet with another column. This is your circular reference. The formula is trying to match itself.

I can see that you want to bring over the Date 1 from Calendar into Electrical Tasks. Which row is it that you want to bring? The MATCH part of the formula would usually be an ID or similar in Electrical that matches with an ID in the Calendar. And this match tells the index which row to use. So my question is - How can we identify which date 1 to put in each row in date 2?

I am logging off now, so do not think I’m ignoring you if you reply and don’t hear back. I will check in again to see if we can get this sorted for you.

johnnipearson

Ultimately, I want to create a "convert to column" formula so new row entries reference the same formula.

So the answer to your question - I want Date (1), row 1 in Calendar - Home Appointments 1 initially. after that I would "convert to column formula"

I noticed I didnt have a ID column. I'll have a play around and watch some tutorials to see if I can get it to work. I have now got ID columns as the primaries on both sheets

johnnipearson

I got it working. Thanks for your help. Certainly pointed me in the right direction!

Cheers,

John

KPH

Great news!