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, please Visit 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
-
Since 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.
-
That is a pretty neat idea.
Craig
-
That'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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives