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

BMar
BMar ✭
edited 12/09/19 in Archived 2016 Posts

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!

Tags:
· Share on FacebookShare on Twitter

Comments

  • BMar
    BMar ✭

    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.

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    That is a pretty neat idea.

     

    Craig

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee

    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.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories