Reference cell in column on one sheet, against column on another.
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!
Best Answer
-
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")
Answers
-
=IF(CONTAINS([Item Name]@row, {MES-Relevent Items Range 1}), true, false)
-
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")
-
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.
-
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 It does not unfortunately, still states false.
-
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"?
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!