Ignore blanks at end of Dynamic Validation List

Hey, sorry for having to post this, but I just cannot figure out how to create a dynamic validation list. I have a list that starts in L6 that re-populates via vba each time the workbook is opened. The number of items on this list will vary each time, and this list is then linked to a data validation list that allows the user to choose the file to open. How do I ignore blanks at the end of my validation list? I don't have any blanks in my list, but the number of blanks at the end will differ each time the workbook is opened.

In the name manager on another project, I used =OFFSET(Main!$O$11,0,0,COUNTA(Main!$O$11:$O$501),1) for a named range and then set my the validation list = to this named range, but when trying to use the same formula (but tweaked a little for my new range) I get an error that says "The Source currently evaluates to an error. Do you want to continue?" when setting my validation list = to this named range. What am I doing wrong here?

Just tried it, got the same error when assigning the named range to the validation list

Originally Posted by JieJenn

Can you post your workbook?

Workbook is attached. I've commented out my vba for the open workbook event so it doesn't run when you open. Pardon the broken link upon opening the file, this literally just reared its head and I haven't had time to rectify it yet. Thanks for taking a look!

Originally Posted by ChemistB

This should be your defined name. You were using the COUNTA function where the row offset is (you don't want to offset from L6) It should be where height is. Like so

=OFFSET('Extract Codes'!$L$6,0,0,COUNTA('Extract Codes'!$L$6:$L795),1)
also you didn't have ' around your sheet name which is necessary when you have blanks in your sheet names.

Did that help?

Excellent. Thanks a bunch! Thanks you too JieJenn!

Thank you too Norie!