Combine multiple Named Ranges into a master Named Range (Column Type Data) and use for data validation in EXCEL 2016 W/O VBA

[darshan singh]

Combine 2 to 3 Named Ranges into a master Named Range (Column Type Data) and use for data validation in EXCEL 2016 W/O VBA

 

[Peter Bartholomew]

The first step is to ditch Excel 2016 as unfit for purpose!
With 365 it would be just

= VSTACK(Table1, Table2)

so saving valuable time.

 

[p45cal]

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:

=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:

=OFFSET(Sheet1!$K$10,,,ROWS(myrng1)+ROWS(myrng2))

For the 3-named range stacking, the formula (see cell M10) is:

=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:

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