Can I use an IF statement with a cross sheet reference

Derek V

I'm trying to use this formula and it returns an Invalid Operation. What am I doing wrong?

=IF({Cross Sheet Range 1} = "", "", IF({Cross Sheet Range 2} = 1, "Yes", "No"))

It works when I reference ranges in the same sheet

Find more posts tagged with

Accepted answers

Derek V

I figured it out!!!

It's an IF statement combined with an Index Match

All comments

kowal

hi @Derek V

of course you can.

Paul Newcome

What exactly is it that you are wanting to accomplish?

Derek V

Hi Paul.

I want to put a green tick in a summary sheet when a certain field in another sheet is filled in. So my logic is if a cell with the project name in the cross sheet reference is blank, then do nothing. If a cell is ticked, then put "Yes" in the summary sheet cell and if it isn't ticked, then put "No".

This formula works when the references are all in the same sheet, but when I substitute the internal references for cross sheet references I get an Invalid Operation error.

=IF([Project Name]@row = "", "", IF([Column9]@row = 1, "Yes", "No"))

Derek V

I figured it out!!!

It's an IF statement combined with an Index Match

Paul Newcome

@Derek V That's exactly what I would have suggested. Glad you were able to get it sorted!

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.