Concatenating two ranges in data validation

[MartinGM]

In a data validation setting I want to show the concatenation of two named ranges.

I can do this by concatenating them on a worksheet with the VSTACK funtion, applying a new range name to the result, and referencing the new range name in the data validation.



That works, but is clumsy and takes some maintenance if the constituent ranges change in size.

I tried creating a named range with the definition =VSTACK(Range 1,Range2) then referencing that name in the data validation, but it wasn't accepted.

Neither did entering this in the data validation setting =Range1 & Range2

Is there another simple way to have two ranges in a data validation setting ?

[Alansidman]

In the attached file, I created a list in columns A and B. I then imported them to Power Query and merged them with a space separator. I then closed them to a table in column D. I then created a data validation in 1F for the values in Column D. I then added values to Column A and B. Clicked on Data Refresh All and the table in Column D is updated. The data validation is also updated in Column F.

[MartinGM]

Thank you.
Not simple, but it works

[Pecoflyer]

Alternatively : What you need to do is write the VSTACK formula, let's say in K1. Then, point the DV list to =K1#
( VSTACK returns an array whereas DV needs a reference to a range)

[MartinGM]

Thanks Pecover. That's very simple, and takes care of the varying size of the concatenated range
I hadn't seen the # suffix before - what does it do ?
Martin

[Pecoflyer]

The hash symbol is used to refer to a spill range in MS365 ( it cannot refer to a "non spill" range)


Any way of adding your XL version to your sig if higher than 2021? ( see mine)