Forum Discussion
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.
Since you have access to a version of Excel loaded with functions, you could write your dynamic arrays without OFFSET.
An example:
=LET(names,$A$1:$A$1000,nonblank,COUNTA(names),TAKE(names,nonblank))
TAKE/DROP are also usable inside of Data Validation.
- Patrick,
Thanks for the example. It appears a bit more intuitive than the OFFSET approach. I'll give that a try.
How about this.....Dr. Excel Manager, Please create an additional VSTACK function named VSTACKR, perhaps, that returns a Range object.Conversely, is there any reason why data validation, like conditional formatting, should not accept Arrays? There is much in the way of strange legacy behaviours that MS prefer not to touch even though the need may be clear.
- Peter,
I've been holding my breath for Visual Studio capabilities to replace VBA.
