Index Match References Disappearing

Sarah_lee123

I am experiencing glitchiness around programmed index match references disappearing. The sheets I have linked will function fine for a period of time and then we will notice the data begins to simply not populate in the receiving sheet. When I look at the index match reference, its gone and I have to remap it. Our formula is correct, , but when I click on the formula to check the sheet reference, it either gives me a blank slate asking me what sheet and columm I am trying to reference (even though it should already remember this information). or it has a different sheet selected and I need to corect it. The formula columns are locked to the users, and we are all on chrome browsers. This is happening at an exhausting rate and I can't figure it out. Any input is appreciated.

Jörg Schmidt1

Hi, maybe a screenshot would help a little bit more to understand your issue.

Do you use any automation concerning cell content?

Joerg🤗

Jörg Schmidt1

Maybe you can track changes out of the activity log and copy/paste entries from there. So, You must not rebuild the formulas from start. Did you make backups from your sheets?

Furthermore, I would recommend to contact support.

Sarah_lee123

@Jörg Schmidt1

Here is a set of screenshots that will help visualize the problem

I built this sheet about a month ago. You can see there are multiple column formulas. (Note: this is not this only sheet that is having this issue, but it is one example of MANY times and MANY sheets this is happening with.)

You can see the formula here

But when I select the reference

It does not show me the reference

This is concerning because I never know which formula is going to ditch its reference sheet until we find errors in the data. Then I can go back and find the formula that is supposed to be pulling in that data. At this point I can fix it and relink, but I can't understand why this is happening. It is a big problem for data integrity and I need to get to the bottom of it and see what I am doing that makes the sheet references disconnect.

It is not possible to audit every formula on a regular basis, just to ensure it is linking correctly. I can't trust the data coming out of this system.

Thanks in advance for your help on this issue.

Paul Newcome

When you highlight the entire reference it will always do the "Reference another sheet", but what happens when you just click somewhere inside of the reference? At that point it should be "Edit Reference".

I do know that I had a sheet that was having very frequent issues like this reverting back to an old cross sheet reference that technically should have no longer existed. I reached out to Support and requested a sheet refresh on their end and have not had any problems with it since.

Sucheta Rajhans

@Sarah_lee123 , is this issue resolved? I am also facing the same issue for multiple apps. Please share if you have received any fix for this. alternatively, have you created a case with smartsheet ?

Sarah_lee123

@Sucheta Rajhans

I was able to follow the above guidance from @Paul Newcome Turns out the references were not disappearing, I was just using the tool incorrectly.

Andrée Starå

Hi @Sarah_lee123

I hope you're well and safe!

Excellent! Glad you got it working!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Sarah_lee123 Happy to help. 👍️

PeggyLang

@Paul Newcome I have noticed this EXACT same issue with a few different sheets. AND most recently I have tried to complete a Find/Replace in CC. The Index/Match formula comes into the sheet(s), however, the reference to another sheet is broken! I have 362 Budget sheets that I need this formula in, I REALLY don't want to create the formula in all 362 sheets!

Any ideas?

Paul Newcome

@PeggyLang Control Center Global Updates cannot create cross sheet references. All they can do is enter a formula. If the cross sheet reference already exists in each of the sheets, it should work fine. If the cross sheet reference did not already exist, then you will have to go into each sheet and create it manually. This is expected behavior unfortunately.