Thursday, December 8, 2011

yogi_Place X In A Cell By Validating Text In Another Cell

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user fk-cwave said:
How can I place a checkmark in a cell by validating text in another cell
In sheet B, I have a column that identifies a customer of being type: Residential, SMB, Enterprise, or Wholesale.  [COLUMN K]
Sample:
COMPANY   TYPE
HP                Enterprise
Sprint            Enterprise, Residential
IBM               Wholesale
In sheet C, I have 1 column for each type.
In sheet C, for each customer I would like to place a check mark in the corresponding customer type column.
COMPANY   ENTERPRISE   RESIDENTIAL   SMB   WHOLESALE  [HEADERS IN ROW 7]
HP                          X
Sprint                      X                        X
IBM                                                                                       X
What formula can I use to fill my table?
This formula only works where the type column in sheet B contains a single type.  I need something that works for multiple type of customers (more like a contains expression):
=if(QUERY('Customer Deployments'!$A$3:$AJ$44;"select K where A="&$A9&"";0)=G$7,"X","")

-------------------------------------------------------------


following is a solution to the problem
Sheet ... base data is in Sheet1, X is marked in Sheet2