Test if cell contains value from list

BMar

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!

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.

J. Craig Williams

That is a pretty neat idea.

 

Craig

Shaine Greenwood

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.