Dynamic named range, ignoring blanks... but on different worksheet?

    Thanks again to Moderator Rob for his answers here.

    As I mentioned in the other (now closed) thread, I had to add your formula in both the "Name Manager" (under "Formulas") *and* in the "Data Validation" (under "Data") menu options (but in "Data Validation", I had to remove any reference to the "Sheet" name, even if referencing the same sheet).

    Today, however, I tried to move the lookups to a different tab. It didn't work. Excel would let me reference a different tab ("worksheet") in the "Name Manager", but not in the "Data Validation" section.

    Am I resigned to hide the rows on the active tab?

    (I have Excel 2007, FWIW)

    Thanks.

    Re: Dynamic named range, ignoring blanks... but on different worksheet?


    I think (and without a current version of your Workbook to test with, I am slightly shooting in the dark here) that all you need to do is change your Data Validation to List and then use =Dates as your Source.

    If thats not working then please upload a Workbook (having removed/disguised confidential information) and someone will figure it out :)

    Re: Dynamic named range, ignoring blanks... but on different worksheet?


    After following those steps, I got the error "The Source currently evaluates to an error. Do you want to continue?"

    I will attempt to clean up the workbook and upload it soon. Thanks.

    (Edit: as a side note, I do not see "Dates" listed in the "Named Cell" ("Name Box"?) section of the worksheet...)

    Edit #2: this is what I have in the "Name Manager":

    =OFFSET(Lookups!$L$2,0,0,COUNTA(Lookups!$L$2:$L$35),1)

    and when I try the same code in the "Data Validation", I get:

    "You cannot use references to other worksheets or workbooks for Data Validation criteria."

    Re: Dynamic named range, ignoring blanks... but on different worksheet?


    The 'Dates' I was refering to was your named range from the previous thread. I have redefined it as:
    Dates, Refers to: =OFFSET(Lookups!$A$2,0,0,COUNTA(Lookups!$A$2:$A$32),1)

    Your reference above was to an empty column which will result in an empty range - hence the error.

    See attached workbook where I have implemented this for your two date columns. You should be able to work out how to extend that to your other validation drop-downs.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login