In a Data Validation List, I have as source a name range called ‘Controls_Add_Items_to_Selector’ that has the following formula
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).
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.
VBA Code:
=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).
VBA Code:
=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.