I have been hollering at my computer all afternoon because I can’t get my data validation scheme to work right, and might have found the problem.
Background: I have a table with many columns. Each group of three columns represents a different characteristic of a common week. So, column 1 is “Item,” column 2 is “Units Completed,” and column 3 is “Manhours,” all for Week 1. Then column 4 is “Item,” column 2 is “Units Completed,” and column 3 is “Manhours,” all for Week 2. So on and so forth. I want to have input to this table validated, but of course “Items” have a different validation requirement than “Manhours.” And, instead of selecting all the “Item” columns and applying one data validation, then selecting all the “Manhours” columns and applying another one (which is tedious, and more prone to error) I am trying to make a smart validation function that knows the current column and applies validation accordingly.
So far: If I want some operation on a cell (such as conditional formatting) to depend on the value of the cell in question, then the formatting formula has to use
INDIRECT(address(row(),column())) [or, for a non-volatile option, I usually use
INDEX($ 1:1000,row(),column())]. Additionally, since the formatting formula can’t take INDIRECT directly (more hollering there), I have to create a named function. Fine, it works.
Therefore, I tried the same with data validation. If I have “Validation criteria” set to “Custom” with a formula of
=IF(OR(COLUMN()=16,COLUMN()=18), INDEX($ 1:$ 1000,ROW(),COLUMN())<8)
and apply them to cells
$ P$ 26:$ R$ 26, I get the expected results: $ P$ 26 can be 7, but not 8; $ Q$ 26 cannot be anything; and $ R$ 26 can 7, but not 8. Awesome.
Because I want the validation to be different lists, I looked into making the list selection dynamic. So, I tried an easy example: named function
list.sel = if( Table_Weekly[@1]="Bolts", list_bolts, list_nuts ), “Validation criteria” set to “List” with a formula of
=list.sel. Works fine and as expected.
I then tried changing the named function:
list.sel = if( column()=16, list1, list2 ) with everything else the same, and it tells me “The Source currently evaluates to an error.” I know I didn’t make a typo because I can enter
=index(list.sel,1) into a cell a in column 16 and it correctly gives me the first item in list1.
So, what gives? Is it something to do with context in which the formula is evaluated? For conditional formatting, Excel knows what cell it is evaluating the formatting function for, so it knows what to do with the row() and column() functions. Does Data Validation not? I’ve been tearing my hair out on this and various other problems all afternoon, and am quite tired of it.