Data validation using VSTACK with dynamic arrays

[S_Cubed]

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.  However when using VSTACK to combine the arrays (VSTACK(dynArray1, dynArray2)), the data validation results in the error:  "The source currently evaluates to an error".  

However, the cell formula =VSTACK(dynArray1, dynArray2) shows the desired list.

What am I missing?

[#2 · Accepted solution]

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

[#3]

@Riny_van_Eekelen  I had implemented the solution you describe since I couldn't get data validation to accept the VSTACK output.  However, I have been able to use dynamic arrays successfully as inputs to data validation.  It's only the VSTACK output that has been problematic.

In any case, the solution you have suggested does work.

[#4]

@S_Cubed 

As @Riny_van_Eekelen suggested, there is a difference between a Range (an area of the worksheet comprising cells and with all sorts of properties such as fill colours and number formats) and an array (a collection of numbers identified by index but with no connection to any particular region of the worksheet). 

INDEX, OFFSET and some new functions such as BYROW or XLOOKUP return references to ranges on the worksheet.  VSTACK and CHOOSEROWS just return arrays of numbers.  This means that data validation and functions such as COUNTIFS work with the first set of functions and not the second.

As Riny says, to make such things work with a dynamic array, you need to output the values to a Range and read them back in again.

[#5]

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.

[#6]

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.

[#7]

@S_Cubed 

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.

[#8]

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.

[#9]

@S_Cubed 

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.

[#10]

Peter,

I've been holding my breath for Visual Studio capabilities to replace VBA.

[#11]

@S_Cubed 

After retiring, I set out to learn Visual Basic and the .Net environment in anticipation of it replacing VBA.  I got that totally wrong and would have been better off focussing on the Web technologies.

Now TypeScript appears to be the way forward is assume there is some room for Visual Studio.  Unfortunately I think I would need business editions of 365 to be able to use PowerAutomate etc.