Google-sheets – How to apply conditional data validation when a cell with a defined value is changed

data validationformulasgoogle sheets

I have a spreadsheet that's basically a grid checklist, let's say 10 columns and 20 rows.

There are multiple cells that have N/A as the value, and I'm worried going through the checklist that one might accidentally be changed.

So I'd like to protect all cells that equal N/A and show a warning if that value is changed. However other cells that do not have N/A should not be validated.

Is this possible, without or with a script?

Best Answer

without a script, you can set up data validation with a custom formula =COUNTIF(A1,"=N/A")
and then copy A1 cell, select all cells in the grid and paste only data validation:

Related Solutions

Google-sheets – Scan row for value, get column of that match, apply column in another formula in a different cell

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!

All together, you're looking at the following formula for B3 in your example:

=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")

edit

I temporarily forgot about filter, which makes this task much easier: =COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")

Google-sheets – Grab data values from Row with max value

You have not mentioned what you would like to so with the three values mentioned, but maybe try:

 =index(B:BB,match(max(A1:A10),A:A,0))

or perhaps:

=index(B:B,match(max(A1:A10),A:A,0))&index(C:C,match(max(A1:A10),A:A,0))&index(E:E,match(max(A1:A10),A:A,0))

or perhaps:

=index(query(A:E,"select B,C,E"),match(max(A1:A10),A:A,0))

Best Answer

Related Solutions

Google-sheets – Scan row for value, get column of that match, apply column in another formula in a different cell

Google-sheets – Grab data values from Row with max value

Related Topic