Nested location within a Drop Down List - Data Validation formula doesn't work right...

Douglas997t  so I pasted your formula into the data validation and it "worked" at least as I would expect:

so I saw "worked" because you don't want to use that -1 after the COUNTA.  If you notice your example that worked counting column B has multiple blank lines at the end of the list because there are actually like 4 other non-blank cells in col B so even with the -1 you still get the full list and more.

That said, this doesn't help you I know.  But I have heard that data validation sometimes has issues when referencing data on a different sheet.  can you create a helper column on the same sheet?  In fact you could use the same formula you wanted to put into the data validation to return the column of cells.  Lets say you did that formula in cell A1 then in the data validation you would only need to enter A1#  (this is assuming you have excel 365).