Variable ranges in dropdown list

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
I need to define a dropdown list (either using a combo box or an in-cell dropdown) but the input range (which contains 10 cells) will not always be fully populated. I tried to define a variable range name using offset to only include populated cells, but I can't get it to work.
Details are:
Cells A1:A10 contain data, but not always full, i.e. sometimes only data in A1:A5 or A1:A8 etc. A1:A10 is named as "InputData"
Cell D1 originally set up with data validation with List on range "=InputData" , but this means blanks can be selected when InputData not full. I then tried to define InputData as "=Offset($A$1,0,0,COUNTA(A1:A10),1)" but the dropdown then shows only blanks.
Any help greatly appreciated.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

xlHammer

Board Regular
Joined
Apr 7, 2008
Messages
215
I tried this putting $ signs around the range,

=Offset($A$1,0,0,COUNTA($A$1:$A$10),1)

and it seemed to work ok

any help?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,491
Messages
5,468,920
Members
406,620
Latest member
Gitani123

This Week's Hot Topics

Top