Results 1 to 2 of 2

Thread: Named Range Gets Lost

  1. #1

    Named Range Gets Lost

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts