Broken formula - linking cells from one SS to another - help!

Hi. Someone on my team mistakenly broke a formula that linked a column called "Construction End Date" between 2 SSs.
Source sheet name: De Novo SS Pipeline
Source sheet column name: Construction End Date

When a Construction End Date is typed in here, it should auto-populate a sheet named De Novo (New Builds).
Target column name is the same: Construction End Date
The linked formula has been there for 4+ years, but I don't know how to restore it - can't find that feature in SS.
I tried editing the formula so it reads like this now:
=IFERROR(INDEX({De Novo SS Construction End}, MATCH(Clinic@row , {De Novo SS Location}, 0)), "")

We have almost identical source and target SSs that use almost the exact same formula, so I thought copying it would work:
=IFERROR(INDEX({Clinic Moves SS Construction End}, MATCH(Clinic@row , {Clinic Moves SS Location}, 0)), "")

However, it's not working. Anyone out there a formula expert who can help?
Is there any way to restore an earlier SS version? I asked our Admin if he could restore to last night's version, but he said you can't do it in SS.

Thank you for any help!
Marvi

Find more posts tagged with

Sheets

Formulas

Cell linking

Accepted answers

All comments

Jgorsich

You can request a snap shot from the "View Activity Log" choice under the file menu, but I've had poor luck with that (it might be my email settings, but I never get the request to approve even when I own the sheet). However, from that same activity log you can see what the old formula was and return it to what it was - presuming that that is how the break occurred.

I've had breaks occur two ways in similar situations - one was by editing/deleting the formula directly, the other was by changing the sheet that was being referenced. So, you'd have to check the activity log in both places (the sheet with the formula and your De Novo sheet) to see what changes occurred (particularly look for deleted rows, columns, and edited formulas.). In one case the edit was the deletion of a row that just duplicated the column names - those names were used in a big 2 dimensional index(match) function and removing them from the source sheet broke a lot of things.

If that doesn't work, include a few screenshots of what your source sheet looks like and someone can likely suggest a formula that will work.

Paul Newcome

You don't need to request a snapshot. The Activity log should store the formula. If it was a column formula, filter the activity log to show column changes for that date and that person. Once you find the log entry for the formula change, you can copy/paste directly from the activity log.

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.