DATA Validation without blanks

[KDS14589]

In a Data Validation List, I have as source a name range called ‘Controls_Add_Items_to_Selector’ that has the following formula

=OFFSET(Controls.General!$P$5,0,0,COUNTA(Controls.General!$G$5:$P$300),1)

It is based on data from cells 5 to 300 in column P of which currently only 5 to 35 have data.

Problem is it gives the data from 5 to 35 but also show blanks up to 300.



This is an ‘old style’ formula I was using that gave me just the data cells only (5 to 35 or more/less).

=Controls.General!$P$5:INDEX(Controls.General!$P$5:$P$300,COUNTIF(Controls.General!$P$5:$P$300,"?*"))

Somewhere in time I started using the ‘offset’ but just now noticed the difference. Can I modify the ‘offset’ or should I go back to the other if I don’t want blanks in the list.

 

[jasonb75]

Offset is not the problem here, COUNTA counts all cells that are not empty (which includes formulas that show a blank cell). If you use COUNTIF, as you have in the other formula then it should work as required.

In most cases I would give preference to INDEX over OFFSET but for what you are doing here there is no advantage or disadvantage to either method.

 

[kevin9999]

In a Data Validation List, I have as source a name range called ‘Controls_Add_Items_to_Selector’ that has the following formula

=OFFSET(Controls.General!$P$5,0,0,COUNTA(Controls.General!$G$5:$P$300),1)

It is based on data from cells 5 to 300 in column P of which currently only 5 to 35 have data.

Problem is it gives the data from 5 to 35 but also show blanks up to 300.



This is an ‘old style’ formula I was using that gave me just the data cells only (5 to 35 or more/less).

=Controls.General!$P$5:INDEX(Controls.General!$P$5:$P$300,COUNTIF(Controls.General!$P$5:$P$300,"?*"))

Somewhere in time I started using the ‘offset’ but just now noticed the difference. Can I modify the ‘offset’ or should I go back to the other if I don’t want blanks in the list.

Just wondering, you have a $G$5 in your first Offset - is that meant to be $P$5?

 

[KDS14589]

Offset is not the problem here, COUNTA counts all cells that are not empty (which includes formulas that show a blank cell). If you use COUNTIF, as you have in the other formula then it should work as required.

In most cases I would give preference to INDEX over OFFSET but for what you are doing here there is no advantage or disadvantage to either method.

Thanks for reply.
I don't know when I started the 'offset' but if there's no difference I'll go back to the 'index' (my favorite anyway)
AGAIN THANKS