Index/Match Cross Sheet References Broken after Control Center Provisioning

Gil Nash

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.

Gil Nash

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.

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.

Cheers,

Genevieve

Gil Nash

@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.

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?

Gil Nash

They all show as broken

Gil Nash

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.

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?

Gil Nash

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.

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.

Gil Nash

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

Gil Nash

AdamT

Gil, did you ever figure this one out? I am also having the same issue.

Gil Nash

@AdamT

We abandoned that endeavor. From what I remember, I never could get the formula to work. I looked at the activity log and my best guess is that I set up Pivots for each employee. I'm assuming that's what the API is referring to in the screenshot. Hope that helps a little.

AdamT

@Gil Nash I solved my use case by adding my reference sheets (two referenced sheets in my index/collect formulas) to my control center blueprint. So each time I provision is creates a static copy of those referenced sheets within the provision and that kept the formulas from breaking. Hope this helps.