Index Match References Disappearing
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.
Answers
-
Hi, maybe a screenshot would help a little bit more to understand your issue.
Do you use any automation concerning cell content?
Joerg🤗
-
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.
-
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.
-
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.
-
@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 ?
-
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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
@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?
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!