I echo
@Peter Bartholomew 's sentiments!
It isn't easy for Excel 2016, however it seems to be done in the attached. Adapted from an idea here:
There are a total of 5
Names in the attached: the 3 source named ranges
myrng1, myrng2 and
myrng3 (these are highlighted in light green), then there are 2 more for use in Data Validation, one called
DataValidtn1 for 2 named ranges to be stacked (
myrng1 &
myrng2) and another, called
DataValidtn2 for 3 named ranges to be stacked (
myrng1, myrng2 &
myrng3).
For the
myrng1 &
myrng2 stacking, on a sheet somewhere (I've used cell K10 but it can be anywhere) the formula:
Code:
=INDEX(IF(ROWS(P$1:P1)<=ROWS(myrng1),myrng1,myrng2),IF(ROWS(P$1:P1)<=ROWS(myrng1),ROWS(P$1:P1),ROWS(P$1:P1)-ROWS(myrng1)))
which has to be array-entered (meaning you have to commit the formula to the cell using
Ctrl+Shift+Enter rather than the usual plain
Enter), then you copy this formula down to at least as many rows as there are rows in
myrng1 &
myrng2 added together.
The named range (dynamic)
DataValidtn1 formula is:
Code:
=OFFSET(Sheet1!$K$10,,,ROWS(myrng1)+ROWS(myrng2))
For the 3-named range stacking, the formula (see cell M10) is:
Code:
=INDEX(IF(ROWS(P$1:P1)<=ROWS(myrng1),myrng1,IF(ROWS(P$1:P1)<=(ROWS(myrng1)+ROWS(myrng2)),myrng2,myrng3)),IF(ROWS(P$1:P1)<=ROWS(myrng1),ROWS(P$1:P1),IF(ROWS(P$1:P1)<=(ROWS(myrng1)+ROWS(myrng2)),ROWS(P$1:P1)-ROWS(myrng1),ROWS(P$1:P1)-ROWS(myrng1)-ROWS(myrng2))))
array entered and copied down as before.
The named range
DataValidtn2 formula is:
Code:
=OFFSET(Sheet1!$M$10,,,ROWS(myrng1)+ROWS(myrng2)+ROWS(myrng3))
In the attached, see cell A2 for 2 range data validation and cell A5 for 3 range DV.