Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Test if cell contains value from list
I have a task list that includes tasks by customer name. Due dates are formula-controlled by an offset from an entry date. Certain customers of ours receive "red carpet" treatment, which requires me to recalculate the due date manually. I would like to try the following automated solution:
Maintain a separate sheet with a list of our Red Carpet customers.
On task sheet, use formula to determine whether the customer for a task matches any of the names from the Red Carpet list.
Is anybody doing something like this? Is it even possible with the client list in a separate sheet?
Thanks for the help!
Comments
-
BMar ✭
自从formulas can't reference cells on other sheets it looks like I can't keep the sheets completely separate. But I do have a workaround for now:
On Red Carpet sheet keep a client list in the first column.
In the first cell of another column concatenate the list with the JOIN function.
On the task sheet create a hidden cell which links the joined list of clients.
For each task, use the formula:
=IF(Customer = "", 0, IF(FIND(Customer, CustomerList) = 0, 0, 1))
It searches through the list for the company name as a string and returns 0 if not found. It does the job, but I wish I could reference other sheets in the formula so I don't have to keep hiding cells to do this kind of computing.
-
J. Craig Williams ✭✭✭✭✭✭That is a pretty neat idea.
Craig
-
Shaine Greenwood EmployeeThat's a fantastic workaround! You're correct in that we don't yet have a way to reference cells on other sheets in formulas, but this is a feature that our Development team is currently working on (although I don't have a timeframe for when this will be released in the application).
I'll get your vote down for this type of feature on our enhancement request list for visibility by the Dev team.
Categories
I hope you're well and safe!<\/p>
There's a known issue at the moment.<\/p>
I'll get back to the post if I can find the thread.<\/p>
I hope that helps!<\/p>
Be safe, and have a fantastic weekend!<\/p>
Best,<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108880,"type":"question","name":"Recover a Row that was accidentally moved then deleted","excerpt":"I was attempting to copy a row from sheet 1 to sheet 2, but I accidentally moved the row then deleted it, but then realized belatedly that the original (now deleted) data in sheet 1 had been moved. Apparently one can not simply use the \"undo action\" arrow after moving something--the option is grayed out as in not…","snippet":"I was attempting to copy a row from sheet 1 to sheet 2, but I accidentally moved the row then deleted it, but then realized belatedly that the original (now deleted) data in sheet…","categoryID":321,"dateInserted":"2023-08-11T22:27:56+00:00","dateUpdated":null,"dateLastComment":"2023-08-12T05:12:42+00:00","insertUserID":165013,"insertUser":{"userID":165013,"name":"GlennJo","title":"Riparian Stewardship Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/GlennJo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-12T03:46:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165013,"lastUser":{"userID":165013,"name":"GlennJo","title":"Riparian Stewardship Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/GlennJo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-12T03:46:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":29,"score":null,"hot":3383611238,"url":"https:\/\/community.smartsheet.com\/discussion\/108880\/recover-a-row-that-was-accidentally-moved-then-deleted","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108880\/recover-a-row-that-was-accidentally-moved-then-deleted","format":"Rich","lastPost":{"discussionID":108880,"commentID":390348,"name":"Re: Recover a Row that was accidentally moved then deleted","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390348#Comment_390348","dateInserted":"2023-08-12T05:12:42+00:00","insertUserID":165013,"insertUser":{"userID":165013,"name":"GlennJo","title":"Riparian Stewardship Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/GlennJo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-12T03:46:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-12T03:46:02+00:00","dateAnswered":"2023-08-11T23:45:38+00:00","acceptedAnswers":[{"commentID":390342,"body":"
