Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have a spreadsheet where I'm using the trailing set of columns as a kind of foreign key; I'm trying to throw an error if someone enters a value that doesn't exist.
I'm thinking one way to do this would be to treat the remaining columns as an array (ie: I2:2) and compare that array to the list of all names (ie: A2:A).
Is there a way to do this?
If their query capability was more sophisticated I could do something like:
SELECT I FROM I2:2 WHERE NOT EXISTS( SELECT 1 FROM A2:A WHERE A=I );
I am not sure of your cell references and Get a list of items missing from array seems quite different from I'm trying to throw an error if someone enters a value that doesn't exist. but considering the last, Data Validation seems to be suitable. For example:
uses ColumnA as a list of valid strings (fruits) against which entries in a range of choice are validated. That range is B1:I20 in the example so B3 elephant and B5 bannana are showing a red warning triangle and a warning message when hovered over.
Change from Show warning to Reject input then trying to enter something like xxxin B7 should result in a window Oops The data that you entered in cell B7 violates validation rules set on this cell. and xxx will not be accepted in B7.
