Forum Discussion
Solved
Data validation using VSTACK with dynamic arrays
I create two distinct dynamic arrays (e.g. dynArray1=OFFSET(rangeName1,0,0,COUNTA(rangeName1),1). Either of the two dynamic arrays can be used successfully as the source for data validation. Howeve...
S_Cubed You're not missing anything, but DV expects a range formula that returns a range. Dynamic array functions don't work. What you need to do is write the VSTACK formula, let's say in K1. Then, point the DV list to =K1#
That should work.
Thanks for the explanation of why my approach didn't work (array vs range).
I do have evidence that a dynamic array created as shown by my original post does work in data validation.
I do have evidence that a dynamic array created as shown by my original post does work in data validation.
Yes, true. That is because OFFSET returns a Range object. Wrap it in ISREF and you get TRUE; use ROW and you get sheet row numbers. Sometimes these things lack any logic; it is just the way they are.