0

I'm trying to set up data validation in a cell so that if any cell in a range in that row has a non-blank cell, the cell with the validation must have a value from its drop-down list. As data is entered with copy/paste, I use "Circle Invalid Data" to find errors, and it's flagging every cell with that validation even if the entire row is blank. What am I doing wrong?

The formula I'm using is =IF(COUNTBLANK($A2:$AF2)<32,States,Blank). Ignore blanks is unchecked. The table "States" has a list of state codes, and the table "Blank" has one blank cell. I have verified that the range and number of cells is correct. I used delete to be sure all the cells were blank.

Improve this question
3
  • 1
    Is your table of states also defined as a named range? As referring to a table generally requires to have a table and column name.
    – gns100
    Commented Sep 12, 2022 at 20:22
  • @gns100 Just the table name can be used without qualification as a function argument, but the function will apply to all the cells in the table. If “States” is a single column, it should work fine.
    – Max R
    Commented Sep 13, 2022 at 0:24
  • The states are a single column named range. Used by itself in a validation it works perfectly, so I'm confident the issue is in the formula.
    – CPC
    Commented Sep 13, 2022 at 22:40

0

Reset to default

You must log in to answer this question.