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

[Douglas997t]

*See attached spreadsheet for references contained in this question...

The goal of my question;

The Drop Down List in cell "B42" works as it should. I would like to modify the underlying formula in this cell's "Data Validation" settings to combine additional functionality in the realm of an automatically updating Drop Down List and have been utilizing the formula that adds this functionality in "H90". Scroll down this page to cell "H90" to see the working test module using the following formula);

=OFFSET($B$90,0,0,COUNTA(B:B)-1)

This formula does exactly what I need except for the updating Drop Down List component. My goal is to combine the aforementioned with the following replacement for $B$90 and of course modifying the "COUNTA(B:B)" component as follows;

=OFFSET('03 - Objection Handling'!$AX$6,0,0,COUNTA('03 - Objection Handling'!AX:AX)-1)

The result of this approach gives me the screen captured image below which is missing the other 35 list entries in the range AX6:AX41 located in the sheet named "03 - Objection Handling".

It seems as though the nested list range location should work but it clearly doesn't. Probably nothing more than a user syntax error but I am not seeing where the error is. Or, maybe this combination won't work together...I don't know at this point!

Thanks in advance for any clarity you can add here!

Douglas

[#2 · Accepted solution]

@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). 

[#3]

Hi "mtarler" and thanks for your potential solution.

I tried removing the " -1 " leaving me with " =OFFSET('03 - Objection Handling'!$AX$6,0,0,COUNTA('03 - Objection Handling'!AX:AX)) " and it did seem to work!

I really appreciate the guidance!

Douglas