My Syntax is correct but I get invalid refernece

The following formula appears correct in that I want to the correct response only if the UPN column is populated and the Construction column cell 1 is Greenfield, one answer if construction column cell 2 is Brownfield then index the exists column to see if the closest parent already exists. if so return "existing" if not return "new" and if the UPN@row is blank then blank.

=IF(UPN@row <> "", IF({Cardinal Metadata - UPN & UPI List 'Construction'} = "Greenfield", "NEW", IF({Cardinal Metadata - UPN & UPI List 'Construction 2'} = "Brownfield", IF(INDEX([Closest Parent]@row, {Cardinal Metadata - UPN & UPI List 'Exists'}, 0), "Existing", "NEW"))), "")

Find more posts tagged with

Accepted answers

All comments

Jeff Reisman

@mifala.chris

Are these references to columns on other sheets?

{Cardinal Metadata - UPN & UPI List 'Construction'}
{Cardinal Metadata - UPN & UPI List 'Construction 2'}
{Cardinal Metadata - UPN & UPI List 'Exists'}

If not, and you are trying to reference columns on the same sheet you are working on, you need to use format of [Column Name]:[Column Name] to indicate a column range. If trying to reference a particular cell in another column, you would use format [Column Name]1 which is the the column name in square brackets followed by the row number ([Column Name]$1 for an absolute reference.)

As far as the rest of your syntax goes, a screenshot of your columns would help sort out what you're doing there.

mifala.chris

Second Smartsheet

The Green cell is for greenfield and the brown is for brownfield the admin would identify this depending on the the project type. and also update the Existing equipment column for the high level equipment that feeding the new equipment in the project. i.e. existing Switchgear feeding new distribution panels and sub panels.

mifala.chris

I was able to get the formula to work using the following syntax after fixing a broken reference, however I am surprised by the need for the IFEROR to Complete the formula since the existing answer now works for a match but the NEW did not

=IFERROR(IF(UPN@row = "", "", IF({Cardinal Metadata - UPN & UPI List 'Construction'} = "Greenfield", "NEW", IF({Cardinal Metadata - UPN & UPI List Range 1} = "Brownfield", IF(INDEX({Cardinal Metadata - UPN & UPI List 'Exists'}, MATCH([Closest Parent]@row, {Cardinal Metadata - UPN & UPI List 'Exists'}, 0)) = [Closest Parent]@row, "Existing", "NEW")))), "NEW")

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.