Formula References Not Updating

I have a sheet that has been in use for three years. It is populated by intake forms. Nothing on the forms or the formulas/references has changed in these three years of use. We have not had any problems with the formulas working.

All of a sudden, when new rows are inserted, the formulas/references have stopped working correctly.

This sheet references a column in the same sheet as a conditional and then looks to another sheet for a set match of data to populate the Cells....

=VLOOKUP([Request Type]2, {Ticket Rules Range 3}, 3, false)

=VLOOKUP([Request Type]2, {Ticket Rules Range 3}, 2, false)

The problem is:

1) the cells have stopped auto-populating although nothing in the sheet or reference sheet seems to have changed.

2) I did simply copy the formula to the blank cells and it does then auto-populate, but it is referencing the Row from which I copied the formula (easily fixed). However, this has to now be done manually.

Any ideas? I think I have checked everything.

Find more posts tagged with

Accepted answers

All comments

Mike TV

There's a limit to the number of cell references a sheet can make I believe. Is it possible a sheet used for 3 years has reached this limit? Can you set up an archive sheet for it to send older rows to so it can clean up some space?

A quick way to tell if this is happening is to make a copy of the sheet and remove some of the very old rows. Then see if it works properly when inserting new rows to the sheet.

Rofonic27

Thanks. I'll try out this suggestion.

Ross C

Following up to see if this worked? Several weeks ago the exact same thing started to happen to me across multiple worksheets, multiple forms, multiple users, etc. My data is about 1.5 years old, but data input twice daily.

Ross C

I deleted data leaving only about 6 months worth, and I am still getting the same issues. I'm having the same issues as the original post. Again, there were no issues with this until a couple of weeks ago and then something changed to start causing the errors. It's happening in numerous different places where forms are used to add data. It seems like I've tried everything including deleting and creating new forms. I even add a line and manually enter, and as soon as I refresh the data changes. The odd thing is the data with the functions are correct for 2-3 seconds, and then reverts to the data from the previous row.

Rofonic27

The above suggestion did not work for me. I removed an entire year's worth and ensured the formulas were all correct on the first three lines. Still the same issue on the new insert.

MMiles

Any update to this? I also have had an index formula reference set up for over a year, it worked fine but stopped working within the last month from what I noticed, my last working refresh was 4/18/22. Nothing has changed in either sheet except overriding the row data (not the headers or column properties) with current monthly data just like I have done month after month for the last year. Seems like something is broken within Smartsheet.

Ross C

I ended up going back and changing my formulas to @row and that solved the issue. Something definitely broke in Smartsheet, but didn't seem like anyone found an easier solution.

MMiles

Thank you Ross. All my formulas were @row and are index matched, they are still not working. I am going back and deleting all the formulas, will try to recreate them and see where that leads me.

MATF12

So yea, just had this issue. I started checking my formulas, all syntax was correct (had to be anyways, locked columns only I could change and I haven't done any modifications). So, decided to delete the connections manually. Started creating the cell references, and boom, it populated. I almost deleted my formulas too as I was going to re input them in, and noticed the changes and scrolled and saw they formulas had started working again. This is definitely something on the Smartsheet itself and the cells, maybe they need to refresh connections every now and then? No idea, but if this happens, can try creating the cell references again. I didn't even have to re input my formula for mine to start working again.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.