Struggling with formula that references status in another sheet

Hello Smartsheet friends!

I have a sheet (called Test Receiver Sheet) where I want a box to be checked for each row if the client code for that row matches client code on the second sheet (called TEST Client Audit Form) AND the status is "Complete".

This is the formula I have so far. Im getting an invalid reference error. =IF(INDEX({TEST Client Audit Form Client Code}, MATCH([Client Code]@row, {TEST Client Audit Form Status}, 0)) = "Complete", 1)

Thank you for any assistance!

Find more posts tagged with

Sheets

Formulas

Cell linking

Accepted answers

All comments

Mike TV

@cugbug

I cannot see where you have your references actually going but it looks like you may have those references out of position on the formula.

After INDEX( I believe you want to have the reference called {TEST Client Audit Form Status} in that spot. Then after the MATCH([Client Code]@row, you want to have the {TEST Client Audit Form Client Code} there. So it would look like:

=IF(INDEX({TEST Client Audit Form Status}, MATCH([Client Code]@row, {TEST Client Audit Form Client Code}, 0)) = "Complete", 1)

cugbug

@Mike TV Thank you for responding!

That did not work, I'm still getting an invalid reference error.

My formula is on the Test Receiver Sheet and it is referencing the TEST Client Audit Form. Does that help? I can provide more screen shots if needed.

Mike TV

@cugbug

Try starting over from scratch. I've had to do that before. Just make sure after INDEX( you're putting the range which has the status "Complete" etc on the other sheet. Then after the MATCH([Client Code]@row, you're putting the reference for the client code on the other sheet. Either delete the references on the sheet with your formula so you can re-create them with a new reference name or give it a new name.

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.