Can someone please help with this formula?

I keep getting an "invalid operation" error message with this formula. Can someone spot where I went wrong? I am not great at formulas. The goal is for multiple criteria (IF statements) to match before entering "Inactive" in the cell.

=IF({Master Cadre Roster Range 1} = [First Name]@row, IF({Master Cadre Roster Range 3} = [Last Name]@row, IF({Master Cadre Roster Range 2} = "Inactive", "Inactive")))

Find more posts tagged with

Formulas

help

Accepted answers

ECarlson_PCG

With your cross sheet references, try this:

=IF(INDEX(COLLECT({Master Cadre Roster Range 2}, {Master Cadre Roster Range 1}, [First Name]@row, {Master Cadre Roster Range 3}, [Last Name]@row), 1) = "Inactive", "Inactive", "")

All comments

ECarlson_PCG

Each "IF" needs a condition after it, you only have that in the final IF portion, for example:

=IF({Master Cadre Roster Range 1} = [First Name]@row, "What you want to happen here", IF({Master Cadre…

are you trying to have both First Name and Last Name populate as "Inactive" ? That would be an "AND" condition.

Lisa LS Kennedy

A range can't equal a cell, a cell can meet criteria in a range. Try something like IF(CONTAINS([First Name]@row, {Master Cadre Roster Range 1}) . Are these AND Statements or OR statements?

Lisa Kennedy

Senior Consultant | Smartsheet Development

Prime Consulting Group

Email: info@primeconsulting.com

ahrenm1

Yes, thank you. These are all AND statements.

ECarlson_PCG

ahrenm1

This worked, thank you so much!

ECarlson_PCG

Great! I'm glad to hear, have a nice weekend!

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.