fixing circular reference

johnnipearson
johnnipearson ✭✭✭✭
edited 02/09/24 in Formulas and Functions

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

Answers

  • KPH
    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
    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))


    Thank you

  • KPH
    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
    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
    johnnipearson ✭✭✭✭

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

    Cheers,

    John

  • KPH
    KPH ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!