4

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 );
1

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:

WA49355 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.