nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Referencing another Smartsheet when #NO MATCH is found

Schneider User 14

Hello,

I've been struggling to find the appropriate formula and AI hasn't helped either.

Here is some context as to what I'm trying to accomplish. In my "calculation worksheet" I have a table with Data Links setup. These all work as intended with no issues. The problem occurs because after an items has been marked completed, it will be removed from the "reference sheet" and put into an "archive sheet" as to not clutter things up.

This is when I run into an issue where I receive a #NO MATCH error because the reference from the "reference sheet" is no longer there but has been moved to the "archive sheet."

Is it possible to create a formula with an IFERROR that can reference both the "reference sheet" and the "archive sheet?" So if the #NO MATCH error appears it then differs to the "archive sheet." Here is what the correct formula that works. (when rows are not deleted)

=IF(NOT(ISBLANK([OI#]@row)), INDEX({connections_3025338826553}, MATCH([OI#]@row, {connections_3025338826537}, 0)))

This is what I have currently came up with for the =IFERROR trying to reference both sheets but it's giving me an #INVALID REF.

=IFERROR(IF(NOT(ISBLANK([OI#]@row)), INDEX({connections_3025338826550}, MATCH([OI#]@row, {connections_3025338826537}, 0))), (IF(NOT(ISBLANK([OI#]@row)), INDEX({connections_3025338826537}, MATCH([OI#]@row, {5944180383088516}, 0)))))

This is a picture of the issue I am running into.

This is the "reference sheet"

This is the "archive sheet"

Picture of working formula (minus the #NO MATCH)

Picture of my new formula (that isn't working)

Any assistance would be much appreciated.

Thank you,

Find more posts tagged with

Formulas

Cell linking

Data links: Column links

sheet reference

Accepted answers

Paul Newcome

Technically your syntax should be working. The "#INVALID REF" error comes from a {Cross Sheet Reference} being typed in but not actually created.

Click inside of each {Cross Sheet Reference} one at a time. You should see "Edit Reference". If you see "Reference Another Sheet", that {Cross Sheet Reference} does not actually exist, and you will need to follow the appropriate steps to create it.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

All comments

Paul Newcome

Technically your syntax should be working. The "#INVALID REF" error comes from a {Cross Sheet Reference} being typed in but not actually created.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Schneider User 14

Good evening Paul,

This helped quite a bit, I was able to get the reference to now match the sheet based on your above method. I had some issues with the sheet references but after some digging I realized my sheet references weren't done correctly. After I made adjustments, it worked like a charm.

Thank you!

Paul Newcome

Happy to help. 👍️

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.