Invalid Data Type or Invalid Column Value on Helper column

I'm trying to create a helper column to evaluate multiple drop down column values. This one is to return a value if the tested columns have a specific value. (Once I get this one working I'll be adding a bunch more but, alas, ran into this issue out of the chute.)

For simplicity here, I've trimmed down my formula to only show two columns I'm testing, but there are a lot more which generated the error the first time so I knocked it down to the bare minimum to generate the error.

=AND([Module 1 Test Status]@row = "Pass", [Module 2 Test Status]@row = "Pass", 1, 0)

Returns #INVALID COLUMN VALUE

However if I change it to

=AND([Module 1 Test Status]@row = "Pass", [Module 2 Test Status]@row = "Pass", 1, 2)

or to

=AND([Module 1 Test Status]@row = "Pass", [Module 2 Test Status]@row = "Pass", "Pass", "Not Pass")

I get #INVALID DATA TYPE

The columns are dropdown lists and restricted to these drop down values:

N/A
Not Run
Pass
Ready to Retest
In Progress
Fail

Find more posts tagged with

Accepted answers

Corey W.

@cdickson Do you have this wrapped in an IF() function that is not shown above? If not, you will want to add that.

All comments

Corey W.

@cdickson Do you have this wrapped in an IF() function that is not shown above? If not, you will want to add that.

Jgorsich

You are missing the if() function to make any of that parseable. Given your error types, it is likely because you cut down your formulas too aggressively though.

What you want is:

=if(and(logical test 1, logical test 2),true result, false result)

In the above, what you have is (you have too much stuff in the and() function):

=and(logical test 1, logical test 2, what you want to be the true result but is being evaluated as logical test 3, what you want to be the false result but is being evaluated as logical test 4)

Once you get that cleaned up, check this sheet:

Formula error messages | Smartsheet Learning Center

Invalid column value comes from having the column formatted for a different type of data, invalid data type comes from having your formulas messed up.

cdickson

Well darn. Thanks! Yep, that solved the issue. Appreciate the quick help!

Deonizia Egan

I think you need an IF statement infront of the And statement.

=IF(AND([Module 1 Test Status]@row = "Pass", [Module 2 Test Status]@row = "Pass"), "Pass", "Not Pass")

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.