Index/Match Cross Sheet References Broken after Control Center Provisioning

I'm having issues with Index/Match formulas to external sheet being broken on new sheet created through provisioning in Control Center?

I have a different blueprint set up in Control Center that contains an Index/Match formula to an external sheet that works as intended and I can't figure out what is different

The external reference sheet in both cases is not being spun up through provisioning each time and is a static sheet outside of template.



  • Basically, I have an intake sheet for self evaluations

    I want to pull in their info into a peer evaluation sheet

    I have columns indexed based on a match of the EmployeeYear columns.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Gil Nash

    You're correct, a cross-sheet formula should still be able to reference that outside sheet without an issue, as long as the original formula had no issue. Can you check the two {references} in the original sheet, in the Blueprint?

    It looks like your formula lower down in the sheet (row 11) is working as expected. Is it possible that there's an error in the referenced sheet, in one of the cells? This would create a domino effect and present an error in the Index(match as well.



  • @Genevieve P. the formulas on row 1 and 11 are referencing a different summary sheet that is getting spun up in provisioning with this sheet and are working correctly. It's the other sheet that isn't populating results.

    It's got to be something minor I'm overlooking but, I've also tried stripping the formulas out of the template and copying them in after provisioning from another sheet where they are working correctly and they don't work in the new sheet either until I update the reference in the formula.

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 02/25/22

    Apologies! I meant row 12, the other green cells.

    After provisioning, can you check the Sheet Reference Manager to see where the references are pointing to?

  • They all show as broken

  • That second screenshot wasn't from a typical result. Most of the time, they just show up as blank (similar to row 12) with broken incoming references.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Gil Nash

    What Workplace is this reference sheet stored in? Is it possible that the user provisioning the new project isn't shared to this other sheet, so the references can't find the connection?

  • No, I checked that first. Our admin account is the owner of the provisioned projects and is set as an admin on the reference sheet. I do have the rows set to lock once info is entered on reference sheet which is the next thing I'm going to try today once in office but I can't imagine that's an issue.

  • Genevieve P.
    Genevieve P. Employee Admin

    You're right, that shouldn't be an issue. The rows can be locked and the cross-sheet reference should still be able to read the content.

    If the references are correct in the original source sheet, then at this point I would suggest working with Smartsheet Support on the issue so that you can set up a screen share and walk through how all the sheets are connected in a private channel.

  • I've already started that process. I appreciate the insight @Genevieve P.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!