Index suddenly stopped working

Hello,

Wondering if anyone has experienced this before and can provide some guidance.

I have a sheet which uses index match in column formulas to pull data from multiple other sheets. This sheet has been working perfectly for several years and has not been edited.

This morning, every index match formula is giving an #error.

Version history shows the errors appeared at the same time on 3 May 2022.

I have closed and reopened, checked that the source data is ok, checked other sheets with similar functionality and this appears to be the only sheet with issues.

I think is has something to do with the index function as the formula works when I take it back to the match only part of the formula

Any ideas on how to fix?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

The #REF error is indicating that a cell reference has been deleted. Did a column get deleted?

IBRIEOG

Hi Paul,

Thanks for the prompt response. As far as I’m aware, no columns have been deleted from any of the (several) reference sheets. I have tried rewriting the formula and can only get the match portion to return the row number. I have also tried duplicating the sheet. When I did this I could get one column formula to index. This morning, several of the formula are now working with no intervention (see RH columns below). However, there are still four columns that return the #ref error when I try to add the index part to the formula.

Andrée Starå

Hi @IBRIEOG

I hope you're well and safe!

Is it a very formula-heavy sheet?
How many rows/columns?
Have you looked at the Activity Log for clues?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

IBRIEOG

Hi Andree

Staying well and safe thanks and hope you are too

Is it a very formula-heavy sheet? Yes, I think it is on the heavy side. See below
How many rows/columns? 82 rows with around 15 index/match columns, and several other columns that have mathematical formulas. Initially much larger (see below) but yesterday I filtered out rows that were no longer needed and deleted in case a maximum had been reached
Have you looked at the Activity Log for clues? Yes, hoping to spot an accidental column deletion etc.! There are no changes to the sheet preceeding the error event except the routine weekly copy of column to an adjacent column about 5 mins before the error is recorded across all index match fields (14K of them) on 3 May.

Paul Newcome

Double check the cross sheet reference for the INDEX function is still valid as well.

IBRIEOG

Hi Paul,

I have tried rewriting the formula in its entirety. The match portion worked but returned an error when index section added.

When the first errors were spotted, I copied the sheet and rewrote the formulas one column at a time. Initially, the same errors were returned across the sheet. I also tried changing the index reference to a different column and then back to the real index column. This worked for some, but not all columns. 12 hours later, half the columns were working and by the following day all the columns were working in the copied sheet and half the columns working in the original.

This morning all columns are back to working in the original sheet. Sample cell history below. I can't identify the trigger for the error nor the resolution, but for now the sheet is behaving as it has for the past several years 😅

Paul Newcome

Honestly it sounds to me as if the sheet just has A LOT going on. When sheets have a large back-end burden (formulas, cross sheet references, conditional formatting, automations, etc.) it tends to slow them down and some processes (in this case formula outputs) may not be finished running by the time the browser finishes loading the sheet.

IBRIEOG

Hi Paul,

Yes, I probably removed several hundred rows when the problem first appeared. The sheet is several years old now and I’m more experienced with Smartsheet, so might be time to review and see if I can refine or get similar results from a report or dashboard. Thank you so much for your patience and expert advice.

liz.mayeux

I have found this issue as well. I have had to change the ending of the formula to show },0)) vs. }),0). I have both versions on the same page.

My new issue is that range names become "invalid" and I have to recreate the quote and rename the range.

Quite frustrating.

Andrée Starå

Hi @liz.mayeux

I hope you're well and safe!

If you haven’t already, I recommend submitting a support ticket through the new Smartsheet Support Portal.

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

liz.mayeux

I have the original problem about the 0) locations already as a ticket because I called their help desk.

The issue w/ my range names becoming invalid is new to me and I only noticed it because I was trying to fix my NOMATCH error messages.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.