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

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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