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"))), "")
Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!