Data Validation Bug

Question

I am using the drop-down for a dynamic list of data.

The list of data starts in cell A3 and can be any number of items long.

Cell A2 has a range name of DataStart (I use the cell directly above the start of the data for the label in case anyone deletes the data by deleting the rows rather than hitting delete and when the headers are protected, this ensures the range name isn't lost).

Cell A1 has a count of of the items in the list starting at A3 down to A500 and this cell has a name range of DataCount. So if there are 75 items in the list then my count returns 75.

Cell C1 has the following formula where you can specify the range of data for the drop-down to create a dynamic list =OFFSET(DataStart,1,0,DataCount). All the relevant check boxes to stop invalid entries are checked but this still allows an invalid entry.

Now if I change the DataStart named range to cell A3 and the Offset formula to =OFFSET(DataStart,0,0,DataCount), this works and stops invalid entries.

Both options effectively do the same thing but the first one doesn't do as it's told!

Answer 1

That would work but sometimes I have lists of data that are text and/or values so the countif formula needs to be adapted to total the rows of data.  Does the counta function deal with all entries?  Sorry but I have closed the file down now and didn't save....

On the whole though my lists of data are held in hidden sheets so having a blank rows 9 times out of 10 isn't necessary so I could just dump the blank row between the header and the start of the list data.

Answer 2

I see what you mean.

It looks to be related to the CountIF function. If you change =COUNTIF(A4:A87,"<>") to =COUNTA(A4:A87) it works. 

I'd call it a bug though, there's no reason that the formula used to count the items should change the way DV operates.

Answer 3

Sorry but I have set out the file incorrectly.  Try the following -

• Name cell A1 as 'Data_Count'
• Enter the formula '=COUNTIF(A4:A87,"<>")' in cell A1
• Enter the text 'Label' in A2
• Name A3 as 'Data_Start'
• Freeze the panes at row 4 (so 1-3 is always visible)
• Enter a, b & c in cells A4-A6
• Enter the value 'Label Value' in cell C3
• Enter following formula for the List option of data validation for cell C4 '=OFFSET(Data_Start,1,0,Data_Count)'

I sometime leave a blank row after a label (A2) as this enables me to use filters in this blank row without obscuring any of the label text or the column automatically changing the width to include the filter drop down when double-clicking the right side of a column.

The above will allow you to enter any value in cell C4 but if you enter the text 'Label' in to cell A3, you cannot enter any value and only the options from the drop-down can be entered.

Thanks

Answer 4

I've tried to recreate the problem and can't. It appears to work for either variation of the formula. The only difference being that in the second case, it includes the column heading in A2 as part of the list.

The only way I could make it fail was to uncheck the "Show error alert....." option on the Error Alert tab of Data Validation.