Reference cell in column on one sheet, against column on another.

Jason Dickson

I am trying to create a formula thagt will look at the "Item Name" columns numbers, and then check to see if any instance of that number appears in a column on another sheet, and if it does return "True", and if it doesnt return "False"

I've had a similar equation in excel "=NOT(ISERROR(MATCH(A2615,'Up-To-DateLK'!A:A,0)))" but I am trying to move away from excel as it is very frustrating to use(equation works sometimes and just decides not to at other times). I cant seem to get this to work in smart sheet though.

I've tried modifying it to "=NOT(ISERROR(MATCH([Item Name]@row{MES-Relevent Items Range 1},0)))" however this gives me a #UNPARSEABLE error.

Any help would be appreciated!

Mike TV

@Jason Dickson

Does it work if you switch to a HAS instead of a CONTAINS?

=IF(HAS({MES-Relevent Items Range 1}, [Item Name]@row), "True", "False")

Mike TV

@Jason Dickson

=IF(CONTAINS([Item Name]@row, {MES-Relevent Items Range 1}), true, false)

Mike TV

@Jason Dickson

The formula I posted above will checkmark a box in a checkbox column. If you want the actual word true or false to display in the cell then put quotation marks around those words.

=IF(CONTAINS([Item Name]@row, {MES-Relevent Items Range 1}), "True", "False")

Jason Dickson

Thank you!

For some reason, the column believes the data is false, despite the entry 1000006 clearly being in the referenced column on the sheet.

Mike TV

@Jason Dickson

Does it work if you switch to a HAS instead of a CONTAINS?

=IF(HAS({MES-Relevent Items Range 1}, [Item Name]@row), "True", "False")

Jason Dickson

@Mike TV It does not unfortunately, still states false.

Mike TV

@Jason Dickson

Have you checked your cross sheet reference (by clicking it in the formula and choosing edit on the tooltip) to make sure you correctly set it up to go to that sheet and that entire column called "Item Number"?

Jason Dickson

@Mike TV It works now! I think excel had done something weird to the numbers, stored them as text maybe?! I Deleted the sheet, and re-exported the info from my database as a CSV, made a new excel file and created a new smartsheet from that, and now the =IF(HAS({MES-Relevent Items Range 1}, [Item Name]@row), "True", "False") formula works! Thank you!