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.