Named Range Gets Lost

Anne Troy

New member
Joined
Mar 25, 2011
Messages
23
Reaction score
0
Points
0
I have a feeling I'm going to be called a scoundrel again, but here goes. I use named ranges to refer to data that changes in number of rows. I need it for a Data Validation drop down. Deb Dag. had a formula that makes a named range dynamic: =OFFSET(ChoiceCategory!#REF!,0,0,COUNTA(ChoiceCategory!$A:$A),1)

Unfortunately, when all the cells are cleared, I get #REF. How can I not lose it, but also not include blanks or the heading in my DV drop down?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I can see the #REF in your formula, but assuming you add =OFFSET($A$1,0,0,COUNTA($A:$A),1) as a DSV List formula, where does #REF pop-up? The DV will still work with an empty column A, it just won't provide a pick list.
 
Top